Top 1 ? - 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 2005 Forums

Transact-SQL (2005)

Top 1 ?

Author

Topic

messedup
Starting Member

2 Posts

Posted - 2010-04-27 : 00:54:38

So far I have returned a list that looks like this:
cola colb colc
x hair No
x finger No
x hair Yes

using distinct and group by and order by

I only want the first result of cola and colb.
desired result is:
cola colb colc
x hair No
x finger No

I cant figure out how to use top 1, or something similiar to get my desired results

Thank you for taking time to look at my issue.

The actual full query as i have it is below:
select distinct JTrequestServiceTaskLink.RequestServiceTaskLinkID,
JTItemType.ItemType,
(Select YesNo=Case
when isnull(NoResult,'') <> 'Negative' then
'No'
else 'Yes' End from JTResultAlcAcetone A where A.ResultID= JTREsultAlcAcetone.ResultID) as YesNo
from JTREsultAlcAcetone
join JTResult
on JTREsultAlcAcetone.resultid =JTResult.ResultID
join JTRequestServiceTaskItemLink
on JTRequestServiceTaskItemLink.requestservicetaskitemlinkid=JTresult.requestservicetaskitemlinkid
join JTrequestServiceTaskLink
on JTrequestServiceTaskLink.requestservicetasklinkid =JTRequestServiceTaskItemLink.requestservicetasklinkid
join JTItem
on JTItem.ItemID=JTRequestServiceTaskItemLink.ItemID
join JTItemTYpe
on JTItemType.ItemTYpeID=JTItem.ItemTYpeID
group by JTREsultAlcAcetone.NoResult, JTItemType.ItemTypeID,JTREsultAlcAcetone.ResultID,
ItemTYpe,JTrequestServiceTaskLink.RequestServiceTaskLinkId, JTItem.ItemID,JTRequestServiceTaskItemLink.ItemID
order by YesNo Asc

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-27 : 01:03:50

what according to you represents the order? how do you determine that x hair with No is first record? there's no concept of first and last in sql table unless you spcify order in terms of some other field (by means of order by). do you have any such column?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

messedup
Starting Member

2 Posts

Posted - 2010-04-27 : 01:08:37

I am ordering by YesNo (colc in the example)
So all the No's should be listed before the Yes'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-27 : 01:38:07

ok then use this:-


SELECT cola,colb,colc
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY cola,colb ORDER BY colc) AS Seq,cola,colb,colc
      FROM Table
)t
WHERE Seq=1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources