sql server programming: Problem to build an SQL statement -- sql server programming

Re: Problem to build an SQL statement

Zach Wells
12/16/2004 4:39:12 PM

sql server programming:

[quoted text, click to view]

How do you determine which values from DetailID and Category to use?

One way is like this:

SELECT ElementID, MIN(DetailID), MIN(Category)
FROM YourTable GROUP BY ElementID

Re: Problem to build an SQL statement

Steve Kass
12/16/2004 8:00:12 PM

Aldo,

If you want the row with the smallest DetailID value for each
ElementID, you can do this (untested for typos)

select ElementID, DetailID, Category
from T as T1
where DetailID = (
select min(T2.DetailID) from T as T2
where T2.ElementID = T1.ElementID
)

Steve Kass
Drew University

[quoted text, click to view]

Re: Problem to build an SQL statement

David Portas
12/16/2004 10:56:41 PM

SELECT elementid, detailid, category
FROM YourTable AS T
WHERE detailid =
(SELECT MIN(detailid)
FROM YourTable
WHERE elementid = T.elementid)

--
David Portas
SQL Server MVP
--

Problem to build an SQL statement

Aldo S.
12/16/2004 11:27:06 PM

Hi,

I have the following details table:

ElementID DetailID Category
10001 2 1482
10001 3 1914
10001 4 1705
10001 5 1683
10001 6 2031
10002 3 1592
10002 4 2144
10002 6 1976
10002 7 1869
10003...
......

Now what I need is an SQL/SP that will allow me to obtain only the first
record that is
different in the ElementID field.

Example, I need a result set that will give me:

ElementID DetailID Category
10001 2 1482
10002 3 1592
10003...
......

What is the SQL to accomplish it ?

Thanks a lot
Aldo

Re: Problem to build an SQL statement

Aldo S.
12/17/2004 6:13:28 AM

Thanks a lot !

Both queries worked perfectly.

Regards
Aldo S.

[quoted text, click to view]

Re: Problem to build an SQL statement

HumanJHawkins
12/18/2004 5:44:07 AM

[quoted text, click to view]
Apologies to Zach if I am wrong, but I think his answer will fail if you
ever have a MIN(Category) that is not related to the MIN(DetailID) from that
set... In other words, it only appeared to work because your Categories and
Details happen to have the same sorting order.

David's example should give the correct answer regardless... Now the only
thing to worry about is whether or not you asked the right question. :-)

Cheers!

Re: Problem to build an SQL statement

David Portas
12/18/2004 9:38:35 PM

There is also no guarantee that my solution will provide only one row per
Elementid. Since Aldo didn't tell us the key of his table we don't know if
Detailids can be duplicated.

An alternative solution worth considering is:

SELECT elementid,
MIN(detailid*10000+category)/10000,
MIN(detailid*10000+category)%10000
FROM YourTable
GROUP BY elementid

(assuming the columns are all INTEGER)

--
David Portas
SQL Server MVP
--

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server programming group.