Exists query with priorities

[shootsie]

I've been presented with a task to do a query similar to the following
and I was curious as to what the quickest query would look like.
Anyone have any ideas??

Some_Id Value
1 A
1 B
1 C
2 C
2 A
2 B
3 B
3 C
4 C
5 Q
5 C
5 R
6 T
7 P
7 B

The problem is that I want to select one record for each ID. If a
record with the value of 'A' exists, then I want to select that record
for that ID. If not, I want to select the record with the value 'B'
for that ID if it exists. Otherwise, just give me the first record for
that ID that exists. The result set would look like this:

Some_ID Value
1 A
2 A
3 B
4 C
5 Q
6 T
7 B

Thanks for your input!

[Hugo Kornelis]

On 18 Mar 2005 14:19:34 -0800, shootsie wrote:

(snip)

Otherwise, just give me the first record for
that ID that exists.

Hi shootsie,

Here's where you run into problems. The data in a table is unordered, so
there is no such thing as a "first" row until you impose a sort order.

The closest I can get with the data you have is

SELECT Some_ID, MIN(Value)
FROM YourTable
GROUP BY Some_ID

But that won't match ALL your expected output.

If you can tell me how to find out "the first row" for an ID, in terms
of the data stored in the table, then I can help you write a query to
get the results you need.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Ervs Sevilla via SQLMonster.com]

--- 1) Put the result set below into a cursor

SELECT DISTINCT id
FROM YourTable

--- 2) Put the 2nd result set into another cursor
SELECT id, valueStr
FROM yourTable
ORDER BY id, valueStr

--- 3) Fetch both cursor values and compare them.
You just have to go thru the loop and get the values
you want.

--
Message posted via http://www.sqlmonster.com

[Hugo Kornelis]

On Fri, 18 Mar 2005 23:37:46 GMT, Ervs Sevilla via SQLMonster.com wrote:

--- 1) Put the result set below into a cursor

SELECT DISTINCT id
FROM YourTable

--- 2) Put the 2nd result set into another cursor
SELECT id, valueStr
FROM yourTable
ORDER BY id, valueStr

--- 3) Fetch both cursor values and compare them.
You just have to go thru the loop and get the values
you want.

Hi Ervs,

Why on earth would you ever want to use two cursors to get a result set
that you could produce lots easier and lots quikcer with one single
query?

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[shootsie]

I suppose "first" isn't as accurate as what I need as "any". It
doesn't really matter what the first row is -- I just need only one
record -- so, maybe the sentence should read:

"Otherwise, just give me any single record for
that ID that exists."

[shootsie]

Although I couldn't get the exact result set mentioned above, I did
come up with this:

select some_id, min(thevalue)
from tbltest
where (thevalue = 'A')
OR (thevalue = 'B' and some_id not in (
select distinct some_id
from tbltest
where thevalue = 'A'))
OR (some_id not in (select distinct some_id
from tbltest
where thevalue = 'A' OR thevalue = 'B'))
Group By some_id

I see what you mean about not having a way to determine what the
"first" record is. Thanks for the help!

[David Portas]

Did you try Hugo's solution:

SELECT Some_ID, MIN(Value)
FROM YourTable
GROUP BY Some_ID

If that doesn't work for you then you'll have to explain why. If you
need more help, please post DDL and sample data as described in the
following article:

http://www.aspfaq.com/etiquette.asp?id=5006

--
David Portas
SQL Server MVP
--

[shootsie]

Thanks David,

Given my example that also would work. I was trying to find a solution
where 'A' and 'B' could be substituted by any value (coincidentally the
example I used puts the priority equal to the value of the field) so I
came up with the above answer. Hugo did manage to simplify things to
the point of embarrassment. :) Thanks!

-Ashley

[David Portas]

Here's another way to define a priority order for the values that
doesn't depend on the alphabetical sort order:

SELECT some_id,
COALESCE(
MIN(CASE WHEN value = 'A' THEN value END),
MIN(CASE WHEN value = 'B' THEN value END),
...
MIN(value)
)
FROM YourTable

[Ervs Sevilla via SQLMonster.com]

whats so hard using cursor? its just like a simple array and thats basic in
computer science.

I was able to get the following:
1 A
2 A
3 B
4 C
5 C
6 T
7 B

But you listed

5 = Q

I dont know why.

--
Message posted via http://www.sqlmonster.com

[Ervs Sevilla via SQLMonster.com]

Sorry my bad..... Hugo's right....
When I compare my result set from Hugo's - it came out the same.

His solution is much better.

--
Message posted via http://www.sqlmonster.com

[David Portas]

> whats so hard using cursor?

Everything! Set based code is generally more efficient, more scalable,
cheaper, easier to develop and manintain, more portable, and easier to
validate and test for correctness. Cursors are rarely a good idea and
almost never the natural first choice for data manipulation tasks.

--
David Portas
SQL Server MVP
--

[shootsie]

Because it's the first value returned for record 5 in the list -- it
could be anything, really.

-Ashley