SQLTeam.com | Weblogs | Forums

Reference alphabet for results from SQL query


#1

I need some help as i dont know where to start...
I have a table with 3 columns. Car ID, Car Make, Car price
I am needing an alphabet, to be returned, with car make that can be a reference point for its ID and Price

Select car_make from table_cars where car_make like 'A%'

Results:
Alpha Romeo
Audi
Acura

For every car found, a dynamic alphabet reference is assigned. Alpha Romeo is returned as Alpha Romeo (A), Audi is returned is Audi (B) and Acura is returned as Acura (C)

Now for every car found, i would like to be able to get the price and reference it by the alphabet used for that car. Such that the results will be

$250,000 (A)
$25,500 (B)
$18,000 (C)


#2

what if you get 27 rows in you result set? what alphabet would it be?


#3
CREATE TABLE #Brands
(
	BrandName	varchar(30)
	, CarPrice	money
)

INSERT INTO #Brands
VALUES('Alpha Romeo', 250000.00),('Audi', 25500.00),('Acura', 18000.00)
,('Beetle', 100.00)

SELECT	[ReferenceLetter] = CHAR(64+ROW_NUMBER()
		OVER
		(
			PARTITION BY LEFT(BrandName, 1)
			ORDER BY BrandName
		))
	, BrandName
	, CarPrice
FROM	#Brands
GO

DROP TABLE #Brands
GO

Next time please provide Create Table and Sample data (INSERT statements) to save us having to do that, as well as expected output. Otherwise someone, and often several people, wind up doing that and ... guess what ... we often guess that wrongly and find we have wasted our time.


#4

should probably be

ORDER BY CarPrice DESC

#5

Indeed! Thanks :slight_smile:


#6

THe max # of cars in table are less than 20. So that shouldn't be a problem. P.S there are few inner and outer joins in there too


#7

Agreed. I should've done that.


#8

Thank you so much. It worked