select top 1 result - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

select top 1 result

Author

Topic

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2009-06-16 : 10:39:33

i have a table like this, where id value appear more then once
with same or diffrent data on column val1 and val2 :

id val1(varchar 50) val2(varchar50)

333 axx fds
333 dfg null
333 ddd xxx
567 sd null
i want to get from this table, all uniuqe values, but with maximum data that there is on val1 and val2, for example :
id = 333 appears 3 time,and got data on both columns on row 1 & 3 ( on row 2 there is a null value on column2)
so i want to get a unique result like this :
333 axx fds
567 sd null

how do i do this?
Thanks
Peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2009-06-16 : 10:48:00

...and what result would you want if the max values appear in different records, like this?:
333 aaa ccc
333 ddd bbb

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2009-06-16 : 11:00:32

the result should be :
333 axx fds
567 sd null
the max value to which i reffer is not by real value but rather then
if val1 is not null then 1 else 0
if val2 is not null then 1 else 0
and the max result will be 2 which means val1 and val2 dont have any null value

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-16 : 11:30:36

quote:
Originally posted by blindman

...and what result would you want if the max values appear in different records, like this?:
333 aaa ccc
333 ddd bbb

pelegk2, you have not answered the above question.

No, you're never too old to Yak'n'Roll if you're too young to die.

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2009-06-16 : 15:49:24

the answer is no!
i want for each id only one result,
where the definition of the rsult as i said was :

quote:

the max value to which i reffer is not by real value but rather then
if val1 is not null then 1 else 0
if val2 is not null then 1 else 0
and the max result will be 2 which means val1 and val2 dont have any null value

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-16 : 15:51:44

webfreds sample data also evaluates as total score of 2.
What to do when several records evaluates as same score?

E 12°55'05.63"
N 56°04'39.26"

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2009-06-16 : 16:24:39

...and he still hasn't answered my question.

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-16 : 16:35:38

Ah, sorry!
I see now you provided the additional sample data...

E 12°55'05.63"
N 56°04'39.26"

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2009-06-16 : 18:17:43

webfred : both of results are good for me
but in the bottom line i want to get only one of this rows,dosent matter which one of them.

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-17 : 02:23:54

[code]-- Prepare sample data
DECLARE @Sample TABLE
(
ID SMALLINT,
Val1 VARCHAR(3),
Val2 VARCHAR(3)
)

INSERT @Sample
(
ID,
Val1,
Val2
)
SELECT 333, 'axx', 'fds' UNION ALL
SELECT 333, 'dfg', null UNION ALL
SELECT 333, 'ddd', 'xxx' UNION ALL
SELECT 567, 'sd', null UNION ALL
SELECT 333, 'aaa', 'ccc' UNION ALL
SELECT 333, 'aaa', 'ccc' UNION ALL
SELECT 333, 'ddd', 'bbb'

-- Stage intermediate date
DECLARE @Stage TABLE
(
rowID INT IDENTITY(1, 1),
ID SMALLINT,
Val1 VARCHAR(3),
Val2 VARCHAR(3)
)

INSERT @Stage
(
ID,
Val1,
Val2
)
SELECT ID,
Val1,
Val2
FROM @Sample
ORDER BY ID,
SIGN(COALESCE(LEN(Val1), 0)) + SIGN(COALESCE(LEN(Val2), 0)) DESC

SELECT s.ID,
s.Val1,
s.Val2
FROM @Stage AS s
INNER JOIN (
SELECT MIN(rowID) AS rowID
FROM @Stage
GROUP BY ID
) AS w ON w.rowID = s.rowID[/code]

E 12°55'05.63"
N 56°04'39.26"

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2009-06-17 : 07:33:42

thanks alot

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources