How can I in SQL filter rows that only has an initial row per id?

How can I in SQL filter rows that only has an initial row per id?

select first row of each group sql server
sql select first record matching criteria
sql left join first match only
sql select first record of each group
sql join only one record from right table
sql group by
sql left join top 1
join two tables and return only one row from second table

I have a table with id and state that are only written to, never updated where I want to query only the id's that have the initial state, i.e. only rows for id's with the first row written, so that I can process them for state changes down the line. If processing goes fine a new state is written for those id's and the next time I query I shouldn't get rows with the id's that were successfully processed.

Example: I have two rows for an id with state 'INITIAL' and 'DONE' and another row for a second id with state 'INITIAL'. When I query I want to only get the second id.

I've tried with group by but since I can't filter on the state without grouping on it too so it didn't work the way I intended.

I came up with this query but is this really how to solve it?

select id, state from states s1 where state = 'INITIAL' and not exists
(select 1 from states s2 where s2.state != 'INITIAL' and s2.id = s1.id);

I'm using Postgres btw :)

I've found many similar questions here but not exactly this, but please direct me to another answer if you know one! :)

Thanks for help!


One option, perhaps the most efficient, uses an NOT EXISTS clause:

SELECT id, state
FROM states s1
WHERE
    state = 'INITIAL' AND
    NOT EXISTS (SELECT 1 FROM states s2 WHERE s1.id = s2.id AND s2.state <> 'INITIAL');

We could also use aggregation here:

SELECT s1.id, s1.state
FROM states s1
INNER JOIN
(
    SELECT id
    FROM states
    GROUP BY id
    HAVING COUNT(CASE WHEN state <> 'INITIAL' THEN 1 END) = 0
) s2
    ON s1.id = s2.id;

Another option is a self-join:

SELECT s1.id, s1.state
FROM states s1
LEFT JOIN states s2
    ON s1.id = s2.id AND s2.state <> 'INITIAL'
WHERE s1.state = 'INITIAL' AND s2.id IS NULL;

This query works by retaining all records where an id is INITIAL, but cannot join to another record having the same id with a state other than INITIAL.

How do I get one row from multiple records in SQL? In all cases, when there is only 1 row for a given AccountID, the row should remain, no problem. But whenever there are 2 rows (there will never be more than 2), I want to keep the row with SDIStatus of 1, and filter out SDIStatus of 2.


Use having cluase

select id from states s1 
group by id
having sum(case when state='INITIAL' then 1 else 0 end)=1
and sum(case when state<>'INITIAL' then 1 else 0 end)=0

How do I get the first row of a group in SQL? MAP sales.acct, TARGET sales.acct, FILTER (@RANGE (2, 2, ID)); You can combine several FILTER clauses in one MAP or TABLE statement, as shown in Table 7-6, which shows part of a Replicat parameter file. Oracle GoldenGate executes the filters in the order listed, until one fails or until all are passed. If one filter fails, they all fail.


Since INITIAL is the first state I made a select of the id's with only 1 row (using count)

SELECT id
FROM (SELECT id, state, COUNT(*) count
      FROM states
      GROUP BY id) as c
WHERE c.count = 1 
  AND c.state = 'INITIAL'

How do I select only one row from duplicate rows in SQL Server? The WHERE clause contains one or more logical expressions that evaluate each row in the table. If a row that causes the condition evaluates to true, it will be included in the result set; otherwise, it will be excluded. Note that SQL has three-valued logic which is TRUE, FALSE, and UNKNOWN.


for loop in SQL. The subquery will run once for each row in the outer query: Use Nested Subqueries if you have an ordered ID column. In our example Then we filter the main widgets table to those IDs. This gets us In the outer subquery, we select only the rows with a row_number of 1. With a similar  A where clause is a condition that must be met in order for a row to be returned in a query. A simple example of a Select statement with a where clause is Select CustomerName from Customers Where State = ’MI’ In this example only customers in the state or Michigan are selected. In order for a row to be returned the expression State = ’MI


SQL code can help you track tons of data from many sources. Unlike joining only the first row, primary and foreign keys won't be of much use, This gives us a familiar table, with one date spanning multiple rows: and then filter to row_num = 1 in the outer query to get just the first record per group. To modify a static row filter. On the Filter Table Rows page of the New Publication Wizard or the Filter Rows page of the Publication Properties - <Publication> dialog box, select a filter in the Filtered Tables pane, and then click Edit. In the Edit Filter dialog box, modify the filter. Click OK. To delete a static row filter


First, it checks at each row in the Visited table to see which ones satisfy the WHERE . It then uses the column names following the SELECT keyword to determine  Consider below table tbl: How can I get only rows with same id but different date. How can I only get id 1 and 3 along with date? Use an AGGREGATE function like MIN or MAX. If you only want one record per ID, you have to choose which date you want. The first (MIN) or the latest (MAX). Edited per clarification in comments: Just use the HAVING to