query help - 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)

query help

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2009-06-18 : 07:59:33

my query is

SELECT     ParticipantID,
                          (SELECT     TOP 1 b.PlacementId
                            FROM          Placements b
                            WHERE      b.ParticipantId = a.ParticipantId
                            ORDER BY PlacementId DESC) AS PlacementId
FROM         Participants a

How can I modify this to list all the participants + the placement record (just the top one as in the query above)
it should retrun the same results as the above query but with more details (fields from each record)

nr
SQLTeam MVY

12543 Posts

Posted - 2009-06-18 : 08:17:25

Your problem is gettinga single row per participent

select a.*, b.*, seq = row_number() over (partition by b.ParticipantId order by b.PlacementId desc)
from Participants a
join Placements b
on b.ParticipantId = a.ParticipantId
where seq = 1

or
select a.*, b.*, seq = row_number() over (partition by b.ParticipantId order by b.PlacementId desc)
from Participants a
join Placements b
on b.ParticipantId = a.ParticipantId
where b.ParticipantId = (select min(c.ParticipantId) from Placements c where c.ParticipantId = a.ParticipantId)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2009-06-18 : 08:24:04

thanks - but I accidentally posted this in the wrong section - just double checked and the server has sql 8 not 2005 - anyway to do this in sql 8?
I want a single row per participant with the last placement record that was inserted.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-06-18 : 08:37:40

Try

select a.*
from Participants a
join Placements b
on b.ParticipantId = a.ParticipantId
where b.ParticipantId = (select min(c.ParticipantId) from Placements c where c.ParticipantId = a.ParticipantId)

Madhivanan

Failing to plan is Planning to fail

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2009-06-18 : 09:28:02

that returns more rows then

SELECT ParticipantID,
(SELECT TOP 1 b.PlacementId
FROM Placements b
WHERE b.ParticipantId = a.ParticipantId
ORDER BY PlacementId DESC) AS PlacementId
FROM Participants a

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2009-06-18 : 13:21:39

it should return the exact number of records that exist in participants and then the details joined of the last record (highest id)
can someone help me with this?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-19 : 13:02:52

[code]
SELECT p.*,pc.*
FROM Participants p
INNER JOIN Placements pc
ON pc.participantId=p.participantId
INNER JOIN (SELECT participantid,MAX(placementId) AS Recent
FROM Placements
GROUP BY participantid) pc1
ON pc1.participantid=pc.participantid
AND pc1.Recent=pc.placementId
[/code]

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2009-06-20 : 15:07:19

that brought 1414 records

there are 2913 records in the participants table and it should show all

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-20 : 22:58:21

KH
[spoiler]Time is always against us[/spoiler]

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2009-06-21 : 02:06:38

[code]
select p.ParticipantID, l.PlacementId
from Participants p
left join
(
select ParticipantId, PlacementId = max(PlacementId)
from Placements
group by ParticipantId
) l on p.ParticipantId = l.ParticipantId
[/code]
this is what I need but I want all the fields from teh participant table and from the placement table

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-06-21 : 02:16:21

[code]
try like this and check it once
select columns.....
from Participants p
left join
(
select *, max(PlacementId)OVER(PARTITION BY ParticipantId) AS PlacementId
from Placements
group by ParticipantId
) l on p.ParticipantId = l.ParticipantId[/code]

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2009-06-21 : 02:22:35

i can't use over partition by as it's sql 8 not 2005

any other ideas? I need all the fields from both tables

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-21 : 02:24:19

quote:

Originally posted by esthera


select p.ParticipantID, l.PlacementId
from   Participants p
       left join
       (
            select ParticipantId, PlacementId = max(PlacementId)
            from   Placements 
            group by ParticipantId
       ) l on p.ParticipantId = l.ParticipantId

this is what I need but I want all the fields from teh participant table and from the placement table


select p.*, l.*
from   Participants p
       left join
       (
            select *,
                   row_no = row_number() over (partition by ParticipantId  order by PlacementId desc)
            from   Placements 
       ) l on p.ParticipantId = l.ParticipantId and l.row_no = 1

KH
[spoiler]Time is always against us[/spoiler]

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2009-06-21 : 02:33:43

'row_number' is not a recognized function name.
it's not 2005 unfortunately

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-21 : 02:35:18

quote:
Originally posted by esthera

'row_number' is not a recognized function name.
it's not 2005 unfortunately

But you posted in a SQL 2005 forum !

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-21 : 02:39:00

[code]
select *
from Participants p
left join Placements l
on p.ParticipantId = l.ParticipantId
and l.PlacementId in (select max(x.PlacementId)
from Placements x
where x.ParticipantId = l.ParticipantId)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2009-06-21 : 02:51:04

I think that's just what I needed - thanks..
and yes I did post in the wrong forum - only realized it afterwards.

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2009-06-21 : 13:49:05

when I try to make this a view - I get an error
OBDC error: column names in each view or function must be unique.
Column name ParticipantId in view or function vwparticipantslplacement is specified more than once

why?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-21 : 13:57:00

Your view is based on 2 tables.
In this tables some columns have the same column name.
You have not to use select *.
Instead you have to list each single column that should be a column in your view.
select p.colname, l.colname ...

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

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2009-06-21 : 14:01:15

I tried that and it didn't work either

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2009-06-21 : 14:11:53

also what am I doing wrong
If I do

select *
from Participants p
left join Placements l
on p.ParticipantId = l.ParticipantId
and l.PlacementId in (select max(x.PlacementId)
from Placements x
where x.ParticipantId = l.ParticipantId) and l.workcityid=366

I get returned all the records even those where the workcityid is not 366

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources