Select a value based on sequence number

Question

Select a value based on sequence number

adonetok

SSCrazy Eights

Points: 8395
More actions
August 9, 2018 at 9:10 am

#362268

There is a table in which there are two columns, sequesenum and color.
The data like below
Seq---Color
1-------Red
2-------Yellow
...
120----Green
How to code to always select a max Seq color?
For example, the result above should be "Green"

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

adonetok - Thursday, August 9, 2018 9:10 AM
There is a table in which there are two columns, sequesenum and color.
The data like below
Seq---Color
1-------Red
2-------Yellow
...
120----Green
How to code to always select a max Seq color?
For example, the result above should be "Green"

What have you tried? This seems to be a simple interview or homework question.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

laurie-789651 SSCertifiable Points: 7680 More actions · Answer 2

Try

SELECT Color FROM mytable WHERE Seq = (SELECT MAX(Seq) FROM mytable)

adonetok SSCrazy Eights Points: 8395 More actions · Answer 3

adonetok

SSCrazy Eights

Points: 8395

August 9, 2018 at 11:36 am

#2001108

Thank you, laurie-789651.
It works.

Luis Cazares SSC Guru Points: 183698 More actions · Answer 4

laurie-789651 - Thursday, August 9, 2018 9:44 AM
Try
SELECT Color FROM mytable WHERE Seq = (SELECT MAX(Seq) FROM mytable)

That involves reading the table more than once, which is not needed.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Joe Torre SSChampion Points: 10248 More actions · Answer 5


DECLARE @t table
   ( Id int IDENTITY
   , HtmlName varchar(40)
   , HexCode char(7)
   , DecimalRGB varchar(13)
   );
INSERT @t VALUES
    ('Black','#000000', '(0,0,0)')
   ,('White','#FFFFFF', '-255255255')
   ,('Red','#FF0000', '(255,0,0)')
   ,('Lime','#00FF00', '(0,255,0)')
   ,('Blue','#0000FF', '(0,0,255)')
   ,('Yellow','#FFFF00', '(255,255,0)')
   ,('Cyan / Aqua','#00FFFF', '(0,255,255)')
   ,('Magenta / Fuchsia','#FF00FF', '(255,0,255)')
   ,('Silver','#C0C0C0', '-192192192')
   ,('Gray','#808080', '-128128128')
   ,('Maroon','#800000', '(128,0,0)')
   ,('Olive','#808000', '(128,128,0)')
   ,('Green','#008000', '(0,128,0)')
   ,('Purple','#800080', '(128,0,128)')
   ,('Teal','#008080', '(0,128,128)')
   ,('Navy','#000080', '(0,0,128)')
   ,('maroon','#800000', '(128,0,0)')
   ,('dark red','#8B0000', '(139,0,0)')
   ,('brown','#A52A2A', '(165,42,42)')
   ,('firebrick','#B22222', '(178,34,34)')
   ,('crimson','#DC143C', '(220,20,60)')
   ,('red','#FF0000', '(255,0,0)')
   ,('tomato','#FF6347', '(255,99,71)')
   ,('coral','#FF7F50', '(255,127,80)')
   ,('indian red','#CD5C5C', '(205,92,92)')
   ,('light coral','#F08080', '-240128128')
   ,('dark salmon','#E9967A', '-233150122')
   ,('salmon','#FA8072', '-250128114')
   ,('light salmon','#FFA07A', '-255160122')
   ,('orange red','#FF4500', '(255,69,0)')
   ,('dark orange','#FF8C00', '(255,140,0)')
   ,('orange','#FFA500', '(255,165,0)')
   ,('gold','#FFD700', '(255,215,0)')
   ,('dark golden rod','#B8860B', '(184,134,11)')
   ,('golden rod','#DAA520', '(218,165,32)')
   ,('pale golden rod','#EEE8AA', '-238232170')
   ,('dark khaki','#BDB76B', '-189183107')
   ,('khaki','#F0E68C', '-240230140')
   ,('olive','#808000', '(128,128,0)')
   ,('yellow','#FFFF00', '(255,255,0)')
   ,('yellow green','#9ACD32', '(154,205,50)')
   ,('dark olive green','#556B2F', '(85,107,47)')
   ,('olive drab','#6B8E23', '(107,142,35)')
   ,('lawn green','#7CFC00', '(124,252,0)')
   ,('chart reuse','#7FFF00', '(127,255,0)')
   ,('green yellow','#ADFF2F', '(173,255,47)')
   ,('dark green','#006400', '(0,100,0)')
   ,('green','#008000', '(0,128,0)')
   ,('forest green','#228B22', '(34,139,34)')
   ,('lime','#00FF00', '(0,255,0)')
   ,('lime green','#32CD32', '(50,205,50)')
   ,('light green','#90EE90', '-144238144')
   ,('pale green','#98FB98', '-152251152')
   ,('dark sea green','#8FBC8F', '-143188143')
   ,('medium spring green','#00FA9A', '(0,250,154)')
   ,('spring green','#00FF7F', '(0,255,127)')
   ,('sea green','#2E8B57', '(46,139,87)')
   ,('medium aqua marine','#66CDAA', '-102205170')
   ,('medium sea green','#3CB371', '-60179113')
   ,('light sea green','#20B2AA', '-32178170')
   ,('dark slate gray','#2F4F4F', '(47,79,79)')
   ,('teal','#008080', '(0,128,128)')
   ,('dark cyan','#008B8B', '(0,139,139)')
   ,('aqua','#00FFFF', '(0,255,255)')
   ,('cyan','#00FFFF', '(0,255,255)')
   ,('light cyan','#E0FFFF', '-224255255')
   ,('dark turquoise','#00CED1', '(0,206,209)')
   ,('turquoise','#40E0D0', '-64224208')
   ,('medium turquoise','#48D1CC', '-72209204')
   ,('pale turquoise','#AFEEEE', '-175238238')
   ,('aqua marine','#7FFFD4', '-127255212')
   ,('powder blue','#B0E0E6', '-176224230')
   ,('cadet blue','#5F9EA0', '-95158160')
   ,('steel blue','#4682B4', '-70130180')
   ,('corn flower blue','#6495ED', '-100149237')
   ,('deep sky blue','#00BFFF', '(0,191,255)')
   ,('dodger blue','#1E90FF', '-30144255')
   ,('light blue','#ADD8E6', '-173216230')
   ,('sky blue','#87CEEB', '-135206235')
   ,('light sky blue','#87CEFA', '-135206250')
   ,('midnight blue','#191970', '(25,25,112)')
   ,('navy','#000080', '(0,0,128)')
   ,('dark blue','#00008B', '(0,0,139)')
   ,('medium blue','#0000CD', '(0,0,205)')
   ,('blue','#0000FF', '(0,0,255)')
   ,('royal blue','#4169E1', '-65105225')
   ,('blue violet','#8A2BE2', '(138,43,226)')
   ,('indigo','#4B0082', '(75,0,130)')
   ,('dark slate blue','#483D8B', '(72,61,139)')
   ,('slate blue','#6A5ACD', '(106,90,205)')
   ,('medium slate blue','#7B68EE', '-123104238')
   ,('medium purple','#9370DB', '-147112219')
   ,('dark magenta','#8B008B', '(139,0,139)')
   ,('dark violet','#9400D3', '(148,0,211)')
   ,('dark orchid','#9932CC', '(153,50,204)')
   ,('medium orchid','#BA55D3', '(186,85,211)')
   ,('purple','#800080', '(128,0,128)')
   ,('thistle','#D8BFD8', '-216191216')
   ,('plum','#DDA0DD', '-221160221')
   ,('violet','#EE82EE', '-238130238')
   ,('magenta / fuchsia','#FF00FF', '(255,0,255)')
   ,('orchid','#DA70D6', '-218112214')
   ,('medium violet red','#C71585', '(199,21,133)')
   ,('pale violet red','#DB7093', '-219112147')
   ,('deep pink','#FF1493', '(255,20,147)')
   ,('hot pink','#FF69B4', '-255105180')
   ,('light pink','#FFB6C1', '-255182193')
   ,('pink','#FFC0CB', '-255192203')
   ,('antique white','#FAEBD7', '-250235215')
   ,('beige','#F5F5DC', '-245245220')
   ,('bisque','#FFE4C4', '-255228196')
   ,('blanched almond','#FFEBCD', '-255235205')
   ,('wheat','#F5DEB3', '-245222179')
   ,('corn silk','#FFF8DC', '-255248220')
   ,('lemon chiffon','#FFFACD', '-255250205')
   ,('light golden rod yellow','#FAFAD2', '-250250210')
   ,('light yellow','#FFFFE0', '-255255224')
   ,('saddle brown','#8B4513', '(139,69,19)')
   ,('sienna','#A0522D', '(160,82,45)')
   ,('chocolate','#D2691E', '(210,105,30)')
   ,('peru','#CD853F', '(205,133,63)')
   ,('sandy brown','#F4A460', '(244,164,96)')
   ,('burly wood','#DEB887', '-222184135')
   ,('tan','#D2B48C', '-210180140')
   ,('rosy brown','#BC8F8F', '-188143143')
   ,('moccasin','#FFE4B5', '-255228181')
   ,('navajo white','#FFDEAD', '-255222173')
   ,('peach puff','#FFDAB9', '-255218185')
   ,('misty rose','#FFE4E1', '-255228225')
   ,('lavender blush','#FFF0F5', '-255240245')
   ,('linen','#FAF0E6', '-250240230')
   ,('old lace','#FDF5E6', '-253245230')
   ,('papaya whip','#FFEFD5', '-255239213')
   ,('sea shell','#FFF5EE', '-255245238')
   ,('mint cream','#F5FFFA', '-245255250')
   ,('slate gray','#708090', '-112128144')
   ,('light slate gray','#778899', '-119136153')
   ,('light steel blue','#B0C4DE', '-176196222')
   ,('lavender','#E6E6FA', '-230230250')
   ,('floral white','#FFFAF0', '-255250240')
   ,('alice blue','#F0F8FF', '-240248255')
   ,('ghost white','#F8F8FF', '-248248255')
   ,('honeydew','#F0FFF0', '-240255240')
   ,('ivory','#FFFFF0', '-255255240')
   ,('azure','#F0FFFF', '-240255255')
   ,('snow','#FFFAFA', '-255250250')
   ,('black','#000000', '(0,0,0)')
   ,('dim gray / dim grey','#696969', '-105105105')
   ,('gray / grey','#808080', '-128128128')
   ,('dark gray / dark grey','#A9A9A9', '-169169169')
   ,('silver','#C0C0C0', '-192192192')
   ,('light gray / light grey','#D3D3D3', '-211211211')
   ,('gainsboro','#DCDCDC', '-220220220')
   ,('white smoke','#F5F5F5', '-245245245')
   ,('white','#FFFFFF', '-255255255');

WITH findMaxColor AS
(SELECT Id
, HtmlName
, HexCode
, DecimalRGB
, Row_Number() OVER
(ORDER BY id DESC) rn
FROM @t
)
SELECT findMaxColor.Id
, findMaxColor.HtmlName
, findMaxColor.HexCode
, findMaxColor.DecimalRGB
FROM findMaxColor
WHERE rn = 1;

Eirikur Eiriksson SSC Guru Points: 182881 More actions · Answer 6

Joe, you missed out the benefits of a clustered index, speeds up the query many times!
😎

First change:
DECLARE @t table ( Id int IDENTITY(1,1) PRIMARY KEY CLUSTERED , HtmlName varchar(40) , HexCode char(7) , DecimalRGB varchar(13) );

And then we can simply do
SELECT TOP(1) T.Id ,T.HtmlName ,T.HexCode ,T.DecimalRGB FROM @t T ORDER BY T.Id DESC;

dave.chintan.m SSC Enthusiast Points: 176 More actions · Answer 7

adonetok - Thursday, August 9, 2018 9:10 AM
There is a table in which there are two columns, sequesenum and color.
The data like below
Seq---Color
1-------Red
2-------Yellow
...
120----Green
How to code to always select a max Seq color?
For example, the result above should be "Green"

You can use either of the below options

Select * from Employee where EmpID = (Select MAX(EmpID) from Employee)

Select top 1 * from Employee order by EmpID DESC