Select Row Based on Column

Question

I have a table with data like the following:

ID    Mfgr    Primary_Mfgr
a1    Acme    P
a1    Bcme    S
a1    Ccme    S
b1    Acme
b1    Bcme
c1    Acme    S
c1    Bcme
d1    Acme
d1    Bcme

I need to create a select statement that will, based on ID return the record with a P as the Primary_Mfgr, if there is no P record, then return the blank record, if those don't exist then return the first S record. In all cases, if there are duplicates return the first result.

Therefore, using the above data I would expect to return the following from four different queries:

Query for a1:

a1    Acme    P

Query for b1:

b1    Acme

Query for c1:

c1    Bcme

Query for d1:

d1    Acme

In every case I need to return just one record. I'm not sure how I would structure to do this. I can easily get back all the 'a1' records for example, but I'm not sure how I would write a query that can take a parameter for ID and still return the correct single record in all circumstances. I'm using SQL Server 2008 R2.

You need to define "first". SQL has no sense of ordering within a table. Your query defines ordering. — Tom H
– Tom H, Commented Jul 11, 2011 at 20:04
I don't particularly care which order they come back in. If there are two the a 'S' just give me the one that comes back from the server first. If there are duplicate types the order becomes unimportant for my needs. — cjbarth
– cjbarth, Commented Jul 11, 2011 at 20:10

Narnian · Accepted Answer · 2011-07-11 20:17:27Z

3

You can just order by the Primary_Mfgr column with a case statement and then select the top 1

SELECT TOP 1 *
FROM Table1
WHERE ID = @ID
ORDER BY 
    CASE Primary_Mfgr 
        WHEN 'P' THEN 1
        WHEN ''  THEN 2
        ELSE 3
    END

edited Jul 11, 2011 at 20:17

answered Jul 11, 2011 at 20:07

Narnian

3,9181 gold badge28 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

gbn Over a year ago

this will only return one row. Not "one row per ID group"

Narnian Over a year ago

He says "In every case I need to return just one record". And he mentions 4 different queries.

gbn Over a year ago

The output would suggest that OP want "one row per group" but doesn't know how. If there are 1.2 million IDs do you suggest 1.2 million queries? Your WHERE is wrong too -1

cjbarth Over a year ago

Awesome; that worked great. Thanks for the quick response. The only thing I changed was the where clause to filter by ID instead of Primary_Mfgr.

Narnian Over a year ago

Yeah, I messed up the ID. Sorry about that.

|

gbn · Accepted Answer · 2011-07-11 20:18:02Z

3

This takes the "first" Mfgr alphabetically (subject to collation etc) where there is more than one for given ID/Primary_Mfgr pair. Otherwise you can change the secondary sort to another column (of you have one) or leave it out to get a random record.

;WITH myCTE AS
(
    SELECT
       *,
       ROW_NUMBER() OVER (
               PARTITION BY ID
               ORDER BY
                  CASE Primary_Mfgr 
                     WHEN 'P' THEN 1 WHEN 'S' THEN 3 ELSE 2
                  END, Mfgr) AS rn
    FROM
       MyTable
)
SELECT *
FROM myCTE
WHERE rn = 1

Edit: you don't have to query once per ID which is silly: this answer does it for all IDs in one go. So your 4 queries above become one.

edited Jul 11, 2011 at 20:18

answered Jul 11, 2011 at 20:08

gbn

434k84 gold badges602 silver badges690 bronze badges

2 Comments

cjbarth Over a year ago

I didn't select this answer because it requires a lot of extra work for the server when in every case I'll know the ID before I execute the query, so it doesn't seem that partitioning by ID makes sense when there is only one ID returned. However, I can see how this might be useful for other cases, so thanks for sharing.

cjbarth Over a year ago

I actually do need to return only one row per request due to some other factors which I haven't included here. I'm actually working to get that requirement changed and if that happens I'll use your answer, but right now only one row at a time can come back.

Collectives™ on Stack Overflow

Select Row Based on Column

2 Answers 2

10 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

10 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related