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

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?

CodePudding user response：

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.*;

CodePudding user response：

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;

CodePudding user response：

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