How to select a name and max integer? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

How to select a name and max integer?

Author

Topic

louiedogg418
Starting Member

28 Posts

Posted - 2011-05-02 : 11:51:00

so i have a dino database and i want to select and return the
dino name and the tallest in the table, this is kinda where i am going but its not working:

select dino_main.Dino_name and max(dino_main.DINO_HEIGHT) from dbo.DINO_MAIN

getting error Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'and'.
in sqlserver 08

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-05-02 : 11:54:08

select dino_main.Dino_name, max(dino_main.DINO_HEIGHT) from dbo.DINO_MAIN group by dino_main.dino_name

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

louiedogg418
Starting Member

28 Posts

Posted - 2011-05-02 : 12:02:27

this returns all the results of the data base, how would i return just the tallest dinosaur and just the name of it?
here is table create and insert code

create table DINO_MAIN
(DINO_ID int primary key not null,
DINO_NAME varchar(50) not null,
DINO_NICK varchar(50) not null,
DINO_HEIGHT int null,
DINO_LENGTH int null,
DINO_WEIGHT int null)

insert into Dino_main
(DINO_ID,DINO_NAME,DINO_NICK,DINO_HEIGHT,DINO_LENGTH,DINO_WEIGHT)
values
(1,'Acrocanthosaurus','Top-Spined lizard',18,40,4000),
(2,'Albertosaurus','Alberta lizard',15,30,6000),
(3,'Allosaurus','Aother lizard',17,40,4000),
(4,'Apatosaurus','deceptive lizard',30,75,66000),
(5,'Archaeopteryx','ancient wing',1,3,21),
(6,'Argentinosaurus','Argentina lizard',70,120,222000),
(7,'Baryonyx','heavy claws',6,30,4000),
(8,'Brachiosaurus','Arm Lizard',50,100,100000),
(9,'Ceratosaurus','horned lizard',13,20,3000),
(10,'Coelophysis','hollow form',3,9,100),
(11,'Compsognathus','elegant jaw',2,3,8),
(12,'Deinonychus','terrible claw',5,9,175),
(13,'Diplodocus','double beam',24,89,50000),
(14,'Dromiceiomimus','emu mimic',5,11,220),
(15,'Gallimimus','fowl mimic',8,18,260),
(16,'Mamenchisaurus','Mamenchi lizard',35,69,60000),
(17,'Megalosaurus','big lizard',12,26,2000),
(18,'Microvenator','small hunter',3,4,14),
(19,'Ornithomimus','bird mimic',8,15,300),
(20,'Oviraptor','egg robber',3,1,80),
(21,'Plateosaurus','flat lizard',7,26,4000),
(22,'Saurornithoides','narrow-clawed lizard',3,7,60),
(23,'Seismosaurus','tremor lizard',84,150,200000),
(24,'Spinosaurus','spiny lizarrd',8,40,14000),
(25,'Supersaurus','super lizard',66,100,120000),
(26,'Tyrannosaurus','tyrant lizard',23,50,14000),
(27,'Ultrasauros','ultra lizard',53,100,140000),
(28,'Velociraptor','swift robber',2,6,250);

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-05-02 : 12:04:27

select Dino_name, DINO_HEIGHT from dbo.DINO_MAIN
where dino_height = (select max(dino_height) from dbo.DINO_MAIN)

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

louiedogg418
Starting Member

28 Posts

Posted - 2011-05-02 : 12:52:47

So if i wanted to combine this with another table and get results on the biggest carnivore how would this work?

select dino_name, dino_height,DINO_FEED
from DINO_MAIN,DINO_FEED
where DINO_MAIN.DINO_ID=DINO_FEED.DINO_ID
AND DINO_HEIGHT = (SELECT MAX(dino_MAIN.dino_height)FROM DINO_MAIN, DINO_FEED AND DINO_FEED = 'CARNIVORE')??
this isnt working but am i close?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-05-02 : 12:53:47

How about you tell us exactly what you want so that we don't have to go back and forth?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

louiedogg418
Starting Member

28 Posts

Posted - 2011-05-02 : 12:56:13

well i want to get results on the biggest carnivore in the database, by name and height and with only showing me the one result like
TREX,50ft carnivore

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-05-02 : 13:00:33

Please show us a data example that illustrates your two-table problem.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

louiedogg418
Starting Member

28 Posts

Posted - 2011-05-02 : 13:26:15

create table DINO_MAIN
(DINO_ID int primary key not null,
DINO_NAME varchar(50) not null,
DINO_NICK varchar(50) not null,
DINO_HEIGHT int null,
DINO_LENGTH int null,
DINO_WEIGHT int null)

create table DINO_FEED
(DINO_ID int null,
DINO_FEED varchar(50) null)

insert into Dino_main
(DINO_ID,DINO_NAME,DINO_NICK,DINO_HEIGHT,DINO_LENGTH,DINO_WEIGHT)
values
(1,'Acrocanthosaurus','Top-Spined lizard',18,40,4000),
(2,'Albertosaurus','Alberta lizard',15,30,6000),
(3,'Allosaurus','Aother lizard',17,40,4000),
(4,'Apatosaurus','deceptive lizard',30,75,66000),
(5,'Archaeopteryx','ancient wing',1,3,21),
(6,'Argentinosaurus','Argentina lizard',70,120,222000),
(7,'Baryonyx','heavy claws',6,30,4000),
(8,'Brachiosaurus','Arm Lizard',50,100,100000),
(9,'Ceratosaurus','horned lizard',13,20,3000),
(10,'Coelophysis','hollow form',3,9,100),
(11,'Compsognathus','elegant jaw',2,3,8),
(12,'Deinonychus','terrible claw',5,9,175),
(13,'Diplodocus','double beam',24,89,50000),
(14,'Dromiceiomimus','emu mimic',5,11,220),
(15,'Gallimimus','fowl mimic',8,18,260),
(16,'Mamenchisaurus','Mamenchi lizard',35,69,60000),
(17,'Megalosaurus','big lizard',12,26,2000),
(18,'Microvenator','small hunter',3,4,14),
(19,'Ornithomimus','bird mimic',8,15,300),
(20,'Oviraptor','egg robber',3,1,80),
(21,'Plateosaurus','flat lizard',7,26,4000),
(22,'Saurornithoides','narrow-clawed lizard',3,7,60),
(23,'Seismosaurus','tremor lizard',84,150,200000),
(24,'Spinosaurus','spiny lizarrd',8,40,14000),
(25,'Supersaurus','super lizard',66,100,120000),
(26,'Tyrannosaurus','tyrant lizard',23,50,14000),
(27,'Ultrasauros','ultra lizard',53,100,140000),
(28,'Velociraptor','swift robber',2,6,250);

insert into DINO_FEED
(DINO_ID,DINO_FEED)
values
(1,'Carnivore'),
(2,'Carnivore'),
(3,'Carnivore'),
(4,'Herbivore'),
(5,'Carnivore'),
(6,'Herbivore'),
(7,'Carnivore'),
(8,'Herbivore'),
(9,'Carnivore'),
(10,'Carnivore'),
(11,'Carnivore'),
(12,'Carnivore'),
(13,'Herbivore'),
(14,'Carnivore'),
(15,'Carnivore'),
(16,'Herbivore'),
(17,'Carnivore'),
(18,'Carnivore'),
(19,'Carnivore'),
(20,'Carnivore'),
(21,'Herbivore'),
(22,'Carnivore'),
(23,'Herbivore'),
(24,'Carnivore'),
(25,'Herbivore'),
(26,'Carnivore'),
(27,'Herbivore'),
(28,'Herbivore');

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-05-02 : 13:34:29

You didn't provide the expected out put, but give this a try:

select dm.Dino_name, dm.DINO_HEIGHT, df.DINO_FEED
from dbo.DINO_MAIN dm
join dino_feed df
on dm.DINO_ID = df.DINO_ID
where dm.dino_height = (select max(dino_height) from dbo.DINO_MAIN) AND
df.DINO_FEED = 'Carnivore'

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

louiedogg418
Starting Member

28 Posts

Posted - 2011-05-02 : 14:08:57

This is returning the column names but with no data in the rows....

IE
dino_name]Dino_height]dino_feed]
and underneath the column names there is no date

louiedogg418
Starting Member

28 Posts

Posted - 2011-05-02 : 14:10:10

and i guess the expected out would show the something like

dino_name]Dino_height]dino_feed]
TREX 60 carnivore

Mikeyboy01
Starting Member

4 Posts

Posted - 2011-05-02 : 14:36:03

The reason why you're not getting any result is because the dino with the MaxHeight is not a carnivore. I'm sure there's probably a better way of writing this but it gets the job done.

SELECT A.DINO_NAME, A.DINO_HEIGHT, B.DINO_FEED
FROM #Dino_main A
LEFT OUTER JOIN #DINO_FEED B ON A.DINO_ID = B.DINO_ID
WHERE A.DINO_HEIGHT = (SELECT MAX(DINO_HEIGHT)
FROM #DINO_MAIN A
JOIN #DINO_FEED B ON A.DINO_ID = B.DINO_ID
WHERE B.DINO_FEED = 'Carnivore')

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-05-02 : 14:39:21

There's a multitude of ways to do it, here's another way:

select top 1 dino_name, max(dino_height)
from dino_main dm
join dino_feed df
on dm.dino_id = df.dino_id
where dino_feed = 'carnivore'
group by dino_name
order by 2 desc

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

louiedogg418
Starting Member

28 Posts

Posted - 2011-05-02 : 16:25:15

that works! so tara what is the reasoning by using "top 1" and ordering the 2 desc?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-05-02 : 16:31:14

Because it's needed if you use the GROUP BY way.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

louiedogg418
Starting Member

28 Posts

Posted - 2011-05-03 : 01:23:36

but how though? I dont understand how adding order by desc 2 works... What does this specific piece do that retunrs the biggest carnivore? if i erase this part and leave it at
select top 1 dino_name, max(dino_height)
from dino_main dm
join dino_feed df
on dm.dino_id = df.dino_id
where dino_feed = 'carnivore'
group by dino_name

it does not return the biggest carnivore

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-05-03 : 12:27:42

You have to use TOP with ORDER BY to get the right one. TOP is not sorted, so it's just bringing back the first one it finds, any of them. ORDER BY sorts it first, then applies TOP. And ORDER BY 2 means to order it by the max(dino_height) as that's the second in the query.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

louiedogg418
Starting Member

28 Posts

Posted - 2011-05-03 : 15:35:24

ahhhh! Thank you so much! Your a pro! :D

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-05-03 : 15:38:36

You're welcome, glad to help.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources