470,579 Members | 2,175 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,579 developers. It's quick & easy.

Exists query with priorities

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!

Jul 23 '05 #1
12 1292
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)
Jul 23 '05 #2

--- 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
Jul 23 '05 #3
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)
Jul 23 '05 #4
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."

Jul 23 '05 #5
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!

Jul 23 '05 #6
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
--

Jul 23 '05 #7
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

Jul 23 '05 #8
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

Jul 23 '05 #9
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
Jul 23 '05 #10

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
Jul 23 '05 #11
> 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
--

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

-Ashley

Jul 23 '05 #13

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by RDRaider | last post: by
3 posts views Thread by John Nagle | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.