Select max and another column

sanna · December 16, 2016, 1:57pm

I thought I had learned something but don´t get this simple query to work.

I have the table Personal with the columns Flygtimmar Name and Befattning

SELECT Namn, Max(Flygtimmar) AS MaxFlygtimmar FROM Personal Where Befattning IN ( 'Pilot' AND 'Flygvärdinna')

And expect to get the max values from the column Flygtimmar and the value from the column Namn for the value Pilot respective Flygvärdinna in the Befattning column but get an error message.

How can I rewrite it?

(sorry for the swedish names on the columns)

ScottPletcher · December 16, 2016, 4:07pm

SELECT Namn, Flygtimmar AS MaxFlygtimmar
FROM (
    SELECT *, ROW_NUMBER() OVER(ORDER BY Flygtimmar DESC) AS row_num
    FROM Personal 
    Where Befattning IN ( 'Pilot' AND 'Flygvärdinna')
) AS derived
WHERE row_num = 1

For me, the Swedish names add some nice flavor to the q!

sanna · December 16, 2016, 5:35pm

I get an error when I try that query to...

ScottPletcher · December 16, 2016, 5:38pm

Sorry, syntax error:

Where Befattning IN ( 'Pilot'. 'Flygvärdinna')

Hmm, now that I look at that more closely, do you mean a given Namn has to include both 'Pilot' AND 'Flygvärdinna' values to be SELECTed in the query? Or just if it contains either one?

sanna · December 16, 2016, 5:42pm

I was thinking the output should return the names of the pilot(s) and the name of the Flygvärdinna who have the max value of the column flygtimmar...

example
Namn Flygtimmar
Brian 4000
Anna 2000

ScottPletcher · December 16, 2016, 6:08pm

SELECT Namn, Flygtimmar AS MaxFlygtimmar
FROM (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY Namn ORDER BY Flygtimmar DESC) AS row_num
    FROM Personal 
    Where Befattning IN ( 'Pilot', 'Flygvärdinna')
) AS derived
WHERE row_num = 1

sanna · December 16, 2016, 8:31pm

This still gives me a list of all the Personal ordered by Their Names

I was thinking the output should return the names of the pilot(s) and the name of the Flygvärdinna who have the max value of the column flygtimmar...

example
Namn Flygtimmar
Brian 4000
Anna 2000

jotorre_riversidedpss.org · December 16, 2016, 11:45pm

SELECT p.Namn, Max(b.Flightnummer) MaxFlightnummer, Sum(b.Pris) SumPris
FROM dbo.Biljetttyp b
JOIN dbo.Passagerare p 
ON b.Biljettyp = p.Biljettyp
WHERE
   p.Flightnummer = 'SK405'
GROUP BY p.Namn ;

sanna · December 19, 2016, 10:01am

I don´t get this
When I only write

SELECT Max(Flygtimmar) AS MaxFlygtimmarpilot FROM Personal Where Befattning = 'Pilot'
SELECT Max(Flygtimmar) AS MaxFLygtimmarflygvardinna FROM Personal Where Befattning = 'Flygvärdinna'

I get only the max values from the column Flygtimmar

But when I

SELECT Personal.Fornamn, Personal.Befattning, Max(Personal.Flygtimmar) AS MaxFlygtimmar
FROM Personal 

WHERE
   Personal.Befattning = 'Pilot' OR Personal.Befattning = 'Flygvärdinna'
GROUP BY Personal.Flygtimmar, Personal.Fornamn, Personal.Befattning;

I get all the values from the table, not just the Max values even though I still have the query Max(Personal.Flygtimmar) AS MaxFlygtimmar

I want just the max values for the 'Pilots' and their names and the Maxvalue for the 'Flygvärdinna' and her name to show.

basit0079 · December 19, 2016, 3:45pm

Hi,

Can you try below code.

SELECT     Fornamn, Befattning, MAX(Flygtimmar) AS Flygtimmar
FROM         dbo.Personal
GROUP BY Fornamn, Befattning
HAVING      (Befattning IN (N'Flygvärdinna', N'Pilot'))

basit0079 · December 19, 2016, 3:48pm

Below data example

Table Name called Personal

Fornamn	Befattning	Flygtimmar
A	Pilot	        125
B	Flygvärdinna	123
B	Flygvärdinna	145
A	Pilot	        160

When pass above query

Fornamn	Befattning	Flygtimmar
B	Flygvärdinna	145
A	Pilot	        160

sanna · December 20, 2016, 7:32am

That´s how I wan´t it.

Maybee my MS sql server management studio that is not working because when I input

SELECT     Fornamn, Befattning, MAX(Flygtimmar) AS Flygtimmar
FROM         Personal
GROUP BY Flygtimmar, Fornamn, Befattning
HAVING      (Befattning IN ('Flygvärdinna', 'Pilot'))

I get the whole table, not just the max values...

    Fornamn	Befattning	Maxflygtimmar
Bibbi	Flygvärdinna	80
Zebra	Flygvärdinna	100
Tova	Flygvärdinna	200
Ullrika	Flygvärdinna	200
Michelle	Pilot	300
Amanda	Flygvärdinna	330
Vera	Flygvärdinna	390
Mohammed	Pilot	400
Torstein	Flygvärdinna	400
Charlie	Flygvärdinna	495
Lara	Flygvärdinna	500
Terese	Flygvärdinna	600
Jet	Flygvärdinna	740
Alvar	Flygvärdinna	800
Eija	Flygvärdinna	800
Tennessee	Flygvärdinna	910
Mary	Pilot	1000
Agnes	Flygvärdinna	1200
Jouko	Pilot	1200
Vassil	Pilot	1200
Emil	Flygvärdinna	1500
Vanja	Flygvärdinna	1700
Neil	Pilot	2125
Gork	Pilot	2670
Neil	Pilot	2900
Amanda	Pilot	3100
Larry	Pilot	3400
Abraham	Pilot	4000
Ugi	Pilot	4000

sanna · December 20, 2016, 7:36am

It is not even ordered with the max flight hours on top...

basit0079 · December 20, 2016, 12:39pm

[quote="sanna, post:13, topic:8823"]
SELECT      Befattning, MAX(Flygtimmar) AS FlygtimmarFROM         dbo.PersonalGROUP BY  BefattningHAVING      (Befattning IN (N'Flygvärdinna', N'Pilot'))
[/quote]

now check the result.

sanna · December 20, 2016, 8:07pm

Yes it is working.
When I write

SELECT     Befattning, MAX(Flygtimmar) AS Flygtimmar
FROM         dbo.Personal
GROUP BY Befattning
HAVING      (Befattning IN ('Flygvärdinna', 'Pilot'))

I get just the max values for each Befattning, but why can´t I add another column from the database?
When I write

SELECT     Fornamn, Befattning, MAX(Flygtimmar) AS Flygtimmar
FROM         Personal
GROUP BY Fornamn, Befattning
HAVING      (Befattning IN ('Flygvärdinna', 'Pilot'))

I get all the rows from the table...

(I don´t know what the N in your query
HAVING (Befattning IN (N'Flygvärdinna', N'Pilot'))
does, but I get the same result if I include or omit them...

basit0079 · December 21, 2016, 1:35pm

Can you please send me all you table data as form of insert query

send me full insert query whatever in table just put all in insert query.

Insert into Personal(Fornamn,Befattning,Flygtimmar) values ('A','Flygvärdinna',120)
Insert into Personal(Fornamn,Befattning,Flygtimmar) values ('A','Flygvärdinna',120)
Insert into Personal(Fornamn,Befattning,Flygtimmar) values ('A','Flygvärdinna',120)
Insert into Personal(Fornamn,Befattning,Flygtimmar) values ('A','Flygvärdinna',120)

sanna · December 21, 2016, 3:58pm

I create the table with

CREATE TABLE Passagerare
(
Nr Integer NOT NULL IDENTITY, 
Namn VarChar(255),
Typ VarChar(30),
Passning Bit,
Platsnr Integer,
Biljettyp VarChar(255),
Flightnummer VarChar(50),
Avgdatum Date
CONSTRAINT PassagerarePK
primary key (Nr),
CONSTRAINT PassagerareFlightFK
foreign key (Flightnummer,Avgdatum) references Flight(Flightnummer,Avgdatum)
ON DELETE CASCADE ON UPDATE CASCADE
);

and

INSERT INTO Personal (Anstnummer,Fornamn,Efternamn,Flygtimmar,Befattning)
VALUES
('N1','Neil','Nelson','2900','Pilot'),
('L1','Larry','Hegetay','3400','Pilot'),
('G1','Gork','Getaway','2670','Pilot'),
('A2','Amanda','Jonsson','3100','Pilot'),
('N2','Neil','Boston','2125','Pilot'),
('M1','Mary','Alstedt','1000','Pilot'),
('J2','Jouko','Uusitalo','1200','Pilot'),
('A4','Abraham','Omar','4000','Pilot'),
('V2','Vassil','Papadimitr','1200','Pilot'),
('U2','Ugi','Abbas','4000','Pilot'),
('M2','Michelle','Easter','300','Pilot'),
('M3','Mohammed','Ebb','400','Pilot'),
('J1','Jet','Rågen','740','Flygvärdinna'),
('B1','Bibbi','Nelson','80','Flygvärdinna'),
('V1','Vera','Hensel','390','Flygvärdinna'),
('C1','Charlie','Channing','495','Flygvärdinna'),
('Z1','Zebra','Wollter','100','Flygvärdinna'),
('A1','Agnes','Ungert','1200','Flygvärdinna'),
('T1','Tennessee','Magden','910','Flygvärdinna'),
('E1','Eija','Merinen','800','Flygvärdinna'),
('U1','Ullrika','Wales','200','Flygvärdinna'),
('A3','Alvar','Ihanen','800','Flygvärdinna'),
('L2','Lara','Esten','500','Flygvärdinna'),
('T2','Terese','Norman','600','Flygvärdinna'),
('T3','Torstein','Mattsen','400','Flygvärdinna'),
('V3','Vanja','Ivanova','1700','Flygvärdinna'),
('E2','Emil','Urban','1500','Flygvärdinna'),
('T4','Tova','Svensson','200','Flygvärdinna'),
('A5','Amanda','Bunkert','330','Flygvärdinna');

it is working to just get the max values
When I write

SELECT     Befattning, MAX(Flygtimmar) AS Flygtimmar
FROM         dbo.Personal
GROUP BY Befattning
HAVING      (Befattning IN ('Flygvärdinna', 'Pilot'))

But why can´t I add another column from the database?

When I write

SELECT     Fornamn, Befattning, MAX(Flygtimmar) AS Flygtimmar
FROM         Personal
GROUP BY Fornamn, Befattning
HAVING      (Befattning IN ('Flygvärdinna', 'Pilot'))

I get all the rows, not just the max values of the Flygtimmar...

basit0079 · December 21, 2016, 4:24pm

for this you need to use subquery for this

you are looking for the max value from Flygtimmar and Befattning and whoes the foreman for that. for you i have created two views.

please run this and check it. From View B you will get the result.

check it and let me know that's your looking for.

CREATE VIEW [dbo].[A]
AS
SELECT     TOP (100) PERCENT Befattning, MAX(Flygtimmar) AS Flygtimmar
FROM         dbo.Personal
GROUP BY Befattning

GO

CREATE VIEW [dbo].[B]
AS
SELECT     dbo.A.Befattning, dbo.A.Flygtimmar, dbo.Personal.Fornamn
FROM         dbo.A LEFT OUTER JOIN
                      dbo.Personal ON dbo.A.Flygtimmar = dbo.Personal.Flygtimmar AND dbo.A.Befattning = dbo.Personal.Befattning

GO

run this

Select * from B

sanna · December 22, 2016, 7:35am

Yes, that´s will make it.
I thought this would be a really simple query but, apparently it wasn´t that simple.

Thanks a lot all who answered.

basit0079 · December 22, 2016, 1:13pm

welcome