How to select the top 1 in case distinct returns 2 rows

Question

I have a select distinct query that can return 2 rows with the same code since not all columns have the same value. Now my boss wants to get the first one. So how to I do it. Below is the sample result. I want only to return the get the first two unique pro

[SQL Related Question must be with some sample data where user can generate same scenario](https://stackoverflow.com/questions/53372997/pivot-query-on-distinct-records) — Prashant Pimpale, Jul 30 '19 at 06:05
What is the uniqueness logic? Can you please explain what row do you want 1 or 2 in your case? — Dmitrij Kultasev, Jul 30 '19 at 06:06
it's just a simple distinct with the columns included. you can picture it yourself just by looking at the result. — Errol Paleracio, Jul 30 '19 at 06:15
why not 2 and 4? remember that the data in SQL Server is stored without an order. The same date can be returned in different order any time. — Dmitrij Kultasev, Jul 30 '19 at 06:17
@DmitrijKultasev it can also be that way. i just need unique pro's. i just can't achieve it with distinct. — Errol Paleracio, Jul 30 '19 at 06:18
what amount do you want to see there? you have it different in both rows. Which one to choose? Is that column is needed for you at all? — Dmitrij Kultasev, Jul 30 '19 at 06:20

DarkRob · Answer 1 · 2019-07-30T06:09:59.150

2

Use row_number in your query. Please find this link for more info link

 ; with cte as (
 select row_number() over (partition by pro order by actual_quantity) as Slno, * from yourtable
 ) select * from cte where slno = 1

edited Jul 30 '19 at 06:09

answered Jul 30 '19 at 06:07

DarkRob

3,843
1
10
27

this will return single record, not distinct values – Dmitrij Kultasev Jul 30 '19 at 06:08

score 1 · Answer 2 · answered Jul 30 '19 at 06:15

Try this-

SELECT * FROM
(
    SELECT *, 
    ROW_NUMBER() OVER (PARTITION BY pro ORDER BY Pro) RN
    -- You need to add other columns in the ORDER BY clause
    -- with 'pro' to get your desired row. other case you 
    -- will get first row returned by the query with only 
    -- order by 'pro' and this can vary for different execution
    FROM your_table
)A
WHERE RN = 1

Dmitrij Kultasev · Accepted Answer · 2019-07-30T06:25:50.953

Your chances to get the proper answer can be much higher if you spend some time to prepare the question properly. Provide the DDL and sample data, as well as add the desired result.

To solve your problem, you need to know the right uniqueness order to get 1 record per window group. Google for window functions. In my example the uniqueness is --> Single row for every pro with earliest proforma_invoice_received_date date and small amount per this date.

DROP TABLE IF EXISTS #tmp;
GO

CREATE TABLE #tmp
    (
      pro VARCHAR(20) ,
      actual_quantity DECIMAL(12, 2) ,
      proforma_invoice_received_date DATE ,
      import_permit DATE
    );
GO

INSERT  INTO #tmp
        ( pro, actual_quantity, proforma_invoice_received_date, import_permit )
VALUES  ( 'N19-00945', 50000, '20190516', '20190517' ),
        ( 'N19-00945', 50001, '20190516', '20190517' )
 ,      ( 'N19-00946', 50002, '20190516', '20190517' )
 ,      ( 'N19-00946', 50003, '20190516', '20190517' );

SELECT  a.pro ,
        a.actual_quantity ,
        a.proforma_invoice_received_date ,
        a.import_permit 
FROM    ( SELECT    pro ,
                    actual_quantity ,
                    proforma_invoice_received_date ,
                    import_permit ,
                    ROW_NUMBER() OVER ( PARTITION BY pro ORDER BY proforma_invoice_received_date, actual_quantity ) AS rn
          FROM      #tmp
        ) a
WHERE   rn = 1;

-- you can also use WITH TIES for that to save some lines of code
SELECT TOP ( 1 ) WITH TIES
        pro ,
        actual_quantity ,
        proforma_invoice_received_date ,
        import_permit
FROM    #tmp
ORDER BY ROW_NUMBER() OVER ( PARTITION BY pro ORDER BY proforma_invoice_received_date, actual_quantity ); 

DROP TABLE #tmp;

This is what I needed. Thanks! – Errol Paleracio Jul 30 '19 at 06:23 — Errol Paleracio, Jul 30 '19 at 06:23

score 1 · Answer 4 · answered Jul 30 '19 at 06:18

CREATE TABLE T (
    A [numeric](10, 2) NULL,
    B [numeric](10, 2) NULL
)

INSERT INTO T VALUES (100,20)
INSERT INTO T VALUES (100,30)
INSERT INTO T VALUES (200,40)
INSERT INTO T VALUES (200,50)

select * 
    from T
/*
A   B
100.00  20.00
100.00  30.00
200.00  40.00
200.00  50.00
*/

select U.A, U.B 
    from
        (select row_number() over(Partition By A Order By B) as row_num, * 
            from T ) U
    where row_num = 1
/*
A   B
100.00  20.00
200.00  40.00
*/

How to select the top 1 in case distinct returns 2 rows

4 Answers4