How to group and pick only certain values based on a field using select query SQL

christy1999 0
Viewed 47

I have a table as follow

ID ORDERNO
1 123
1 123
2 456
2 456

During every select query done via application using JDBC, only the grouped records based on ORDERNO should be picked.

That means, for example, during first select query only details related to ID = 1, but we cannot specify the ID number in where clause because we do not know how many number of IDs will be there in future. So the query should yield only one set of records; application will delete those records after picking, hence next select query will result in picking other set of records. How to achieve it?

greatest-n-per-group tsql sql-server sql
3 Answers

You can use TOP WITH TIES for this

SELECT TOP (1) WITH TIES
  t.ID,
  t.ORDERNO
FROM YourTable t
ORDER BY
  t.ID;

If you want to select and delete at the same time you could delete using an OUTPUT clause

WITH cte AS (
    SELECT TOP (1) WITH TIES
      t.ID,
      t.ORDERNO
    FROM YourTable t
    ORDER BY
      t.ID
)
DELETE cte
OUTPUT deleted.*;
Charlieface0

order your rows and select top n number of rows that you want :

select top (1) with ties ID, ORDERNO
from tablename
order by ID asc
eshirvana0

As one option you could select on the MIN(ID) like:

SELECT *
FROM yourtable
WHERE ID = (SELECT MIN(ID) FROM yourtable);

You could also use window functions to do this:

SELECT ID, ORDERNO
FROM 
    (
        SELECT ID, ORDERNO
            DENSE_RANK() OVER (ORDER BY ID ASC) AS dr
        FROM yourtable
    )dt
WHERE dr = 1;
JNevill0
Related