SQLTeam.com | Weblogs | Forums

Query


#1

Hi

I have 1 query which returns record order by ID like this. Now i want that in addition to the below Query i want another query to group by Name thru Stored Procedure . Since A is at top first record with Name 'A' top 3 records are displayed . Then with name C , Then B & so on

ID                                     Name

1000 A
990 C
980 A
970 A
960 B
950 C
930 A

Final Data Should be like this
ID Name
1000 A
980 A
970 A
990 C
950 C
960 B


#2
;with cte as
(
	select distinct [Name]
	from YourTable
)
select
	a.Id,
	b.[Name]
from
	cte b
	cross apply
	(
		select top (3) Id
		from
			YourTable y
		where
			y.[Name] = b.[Name]
		order by
			Id desc
	) as a;

#3

Hi James

Can u tell me these 2 lines
select
a.Id,
b.[Name]

Thanks


#4

Hi

Secondly Name A does not appear at top when i check this statement
;with cte as
(
select distinct [Name]
from #contact1
)

select distinct [Name]
from #contact1

when i simply writes select projectname from #contact1 then it is o.k . But with distinct it changes sequence . Why is it so.
Thanks


#5

You need to use cte in the bottom or why use a cte. Also you need an order by.