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

Question

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?

Answer 1

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;