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, Jul 11 '11 at 20:04
This reminds me of: http://stackoverflow.com/questions/4485965/t-sql-conditional-where-clause — OMG Ponies, Jul 11 '11 at 20:06
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, Jul 11 '11 at 20:10

Narnian · Accepted Answer · 2011-07-11T20:17:27.417

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 '11 at 20:17

answered Jul 11 '11 at 20:07

Narnian

3,858
1
26
29

this will only return one row. Not "one row per ID group" – gbn Jul 11 '11 at 20:09
He says "In every case I need to return just one record". And he mentions 4 different queries. – Narnian Jul 11 '11 at 20:11
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 – gbn Jul 11 '11 at 20:15
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. – cjbarth Jul 11 '11 at 20:16
Yeah, I messed up the ID. Sorry about that. – Narnian Jul 11 '11 at 20:17
Ok, gbn, I didn't interpret his request the same way. Perhaps you're right. – Narnian Jul 11 '11 at 20:19
I've changed to +1: I looked at it and saw "do it in one set based query"... your simpler solution is what OP wants after all. Although, see the comment to my answer. – gbn Jul 11 '11 at 20:22
My database is on one partition, so I'm not familiar with some of the code. That is an intriguing way to do this. – Narnian Jul 11 '11 at 20:25
The syntax 'PARTITION BY' has nothing to do with your database being on one or many partitions. The 'PARTITION BY' syntax is extremely useful for many things. You might want to give that a Google. – cjbarth Jul 11 '11 at 20:59
Interesting note: I was testing this solution and didn't limit by ID on accident and my result set didn't come back properly. This worked great and very fast, but for anything over a single ID gbn's answer is the way to do this. – cjbarth Jul 15 '11 at 23:04

gbn · Answer 2 · 2011-07-11T20:18:02.730

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 '11 at 20:18

answered Jul 11 '11 at 20:08

gbn

422,506
82
585
676

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 Jul 11 '11 at 20:18
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. – cjbarth Jul 11 '11 at 20:21

Select Row Based on Column

2 Answers2