possible sql cursor question

[thefrstgatsby]

I am retrieving info from a table, and this is the problem


assume I want to retrieve the top 50 people, based on how much money they have, and I want to list the cars they drive.

if I just select, amount of money, name and car, and order by money, I get this.

200$ mike ferrari
200$ mike lamborghini
5$ matt pinto
5$ matt sunfire
5$ matt chevy


but that's not really the top 50 by money, because they are duplicated.

So I want the list to come out like this

200$ mike ferrari, lamborghini
5$ matt pinto, sunfire, chevy

now it's a top 50 (or 2) because it only lists the people and their money once.

What do I need to do to get these results?

 

[JamesLean]

One possible solution. Create a function that will return a comma-separated list of cars given the user id/name. Then just call that in your select:

CREATE FUNCTION fnGetCars(
  @userid int
)
RETURNS varchar(1000)
AS
BEGIN
  DECLARE @carlist varchar(1000)
  SET @carlist = ''

  --get list of cars for this user
  SELECT @carlist = @carlist + car + ', '
  FROM t
  WHERE userid = @userid

  --chop off trailing comma
  IF @carlist <> ''
    SET @carlist = LEFT(@carlist, LEN(@carlist) - 2)

  RETURN @carlist
END

SELECT TOP 50 money, name, dbo.fnGetCars(userid)
FROM t
ORDER BY money DESC

--James

 

[JamesLean]

Oops - that SELECT is wrong.

SELECT money, name, dbo.fnGetCars(userid)
FROM (
    SELECT DISTINCT TOP 50 money, name, userid
    FROM t
    ORDER BY money DESC
  ) t
ORDER BY money DESC

--James

 

[donutman]

A slightly different version using James' idea:

[Blue]CREATE[/Blue] [Blue]FUNCTION[/Blue] fnGetCars
  [Gray]([/Gray]@Money [Blue]money[/Blue][Gray],[/Gray]
   @Name [Blue]varchar[/Blue][Gray]([/Gray]25[Gray])[/Gray][Gray])[/Gray]
[Blue]RETURNS[/Blue] [Blue]varchar[/Blue][Gray]([/Gray]1000[Gray])[/Gray]
[Blue]AS[/Blue]
[Blue]BEGIN[/Blue]
  [Blue]DECLARE[/Blue] @CarList [Blue]varchar[/Blue][Gray]([/Gray]1000[Gray])[/Gray][green]
  --get list of cars for this group
[/green]  [Blue]SELECT[/Blue] @CarList [Gray]=[/Gray] @CarList [Gray]+[/Gray] 
            [Fuchsia]Coalesce[/Fuchsia][Gray]([/Gray][red]', '[/red] [Gray]+[/Gray] Car[Gray],[/Gray] Car[Gray])[/Gray]
  [Blue]FROM[/Blue] t
  [Blue]WHERE[/Blue] [Name][Gray]=[/Gray]@Name [Gray]AND[/Gray] [Money][Gray]=[/Gray]@Money
  [Blue]RETURN[/Blue] @CarList
[Blue]END[/Blue]

[Blue]SELECT[/Blue] [Blue]TOP[/Blue] 50 [Money][Gray],[/Gray] [Name][Gray],[/Gray] 
       dbo.fnGetCars[Gray]([/Gray][Money][Gray],[/Gray] [Name][Gray])[/Gray]
   [Blue]FROM[/Blue] t
   [Blue]GROUP[/Blue] [Blue]BY[/Blue] [Money][Gray],[/Gray] [Name]
   [Blue]ORDER[/Blue] [Blue]BY[/Blue] [money] [Blue]DESC[/Blue][Gray],[/Gray] [Name]

But UserID if there is one, would probably be more appropriate than name.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[donutman]

[Blue]SELECT[/Blue] @CarList [Gray]=[/Gray] @CarList [Gray]+[/Gray] 
            [Fuchsia]Coalesce[/Fuchsia][Gray]([/Gray][red]', '[/red] [Gray]+[/Gray] Car[Gray],[/Gray] Car[Gray])[/Gray]

Should have been:

[Blue]SELECT[/Blue] @CarList[Gray]=[/Gray][Fuchsia]Coalesce[/Fuchsia][Gray]([/Gray]@CarList[Gray]+[/Gray][red]', '[/red][Gray],[/Gray]@CarList[Gray])[/Gray] [Gray]+[/Gray] Car

-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[thefrstgatsby]

the @carlist in select statement from the fngetcars function, is that supposed to be the name of the cars column?

 

[thefrstgatsby]

And what is the Car part at the end of the select statement? I didn't see that declared anywhere.. or is that the column name and @CarList=Coalesce(@CarList+', ',@CarList) + Car is setting the field to the previous car then "," then the next car?

 

[donutman]

if I just select, amount of money, name and car, and order by money, I get this.

We both used that to deduce the names of your columns. Yes to the questions.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[thefrstgatsby]

Also, I'm not sure if this would work, since the data is coming from multiple sources.

I.E. A car table and a names table ( which contains the names and the money)

 

[donutman]

Doesn't matter, you just do the proper join.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[thefrstgatsby]

ok so far I've implemented your code, and separately checked the syntax of the function as well as the select statement and they check out. But when I implement the function, I get

Server: Msg 156, Level 15, State 1, Procedure fnGetCars, Line 28
Incorrect syntax near the keyword 'select'.


what does this usually mean (where should I look for the error?)

 

[donutman]

I'd take a wild guess that on line 28 of your function, you have a syntax error near the keyword select.
Without a listing, I can't be more specific than that.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[donutman]

I have a plane to catch, so I won't be able to help you for several hours.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[thefrstgatsby]

line 28 is the select statement that calls the function. When I remove the function call in the statement and rem the function itself, the statement works.

 

[thefrstgatsby]

Also, for the variables that I declare for the function:

@money and @name

if one of them is a unique identifier, does that change anything, or do I just declare it as

CREATE FUNCTION fnGetCars
(@Name uniqueidentifier(100))
?