Query Syntax: max

Showing posts with label max. Show all posts

Wednesday, March 28, 2012

Query with MAX funtion

Hello,
I have 2 tables.
one table contains employees with their salary.
second table contains department.
I can select the highest salary in each department but
I would like to select the name of the employee who makes the highest salary in each department.
Can you help me to make this query ?
Thanks,
Aur=E9lieThis is a multi-part message in MIME format.
--=_NextPart_000_033B_01C37935.CF55CC40
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Try:
select
d.DeptName
, e.EmployeeName
from
Depts as d
join
Employees as e on e.Dept =3D d.Dept
where
e.Salary =3D
(
select
max (e2.Salary)
from
Employees as e2
where
e2.Dept =3D d.Dept
)
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Aur=E9lie" <av@.lbn.fr> wrote in message =news:294501c37955$7b0322d0$a601280a@.phx.gbl...
Hello,
I have 2 tables.
one table contains employees with their salary.
second table contains department.
I can select the highest salary in each department but
I would like to select the name of the employee who makes the highest salary in each department.
Can you help me to make this query ?
Thanks,
Aur=E9lie
--=_NextPart_000_033B_01C37935.CF55CC40
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Try:
select
=d.DeptName
, e.EmployeeName
from
Depts as =d
join
Employees as =e on e.Dept =3D d.Dept
where
e.Salary ==3D
(
=select
= max (e2.Salary)
=from
= Employees as e2
=where
= e2.Dept =3D d.Dept
)
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Aur=E9lie" =wrote in message news:294501c37955$7b=0322d0$a601280a@.phx.gbl...Hello,I have 2 tables.one table contains employees with their =salary.second table contains department.I can select the highest salary in each =department butI would like to select the name of the employee who makes the =highest salary in each department.Can you help me to make this query ?Thanks,Aur=E9lie

--=_NextPart_000_033B_01C37935.CF55CC40--|||This is a multi-part message in MIME format.
--=_NextPart_000_0016_01C37937.E00063F0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Tom's Query will work provided that the Salary field is declared as a =money or decimal field. I have seen many databases where (for =portability and the capability of storing very large numbers) that money =field are defined as "float" columns. If this is the case you should =use a 'delta' value when comparing the salaries - because of computer =rounding errors you should never directly compare floats, doubles, =anything with a sliding decimal...so the comparison would be:
where abs(e.salary - (select max (e2.Salary) from Employees as e2 where
e2.Dept =3D d.Dept) ) < 0.0001
Bruce Carson
Director of Technology
Edgewater Technology
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:%23QjHCdVeDHA.2680@.TK2MSFTNGP11.phx.gbl...
Try:
select
d.DeptName
, e.EmployeeName
from
Depts as d
join
Employees as e on e.Dept =3D d.Dept
where
e.Salary =3D
(
select
max (e2.Salary)
from
Employees as e2
where
e2.Dept =3D d.Dept
)
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Aur=E9lie" <av@.lbn.fr> wrote in message =news:294501c37955$7b0322d0$a601280a@.phx.gbl...
Hello,
I have 2 tables.
one table contains employees with their salary.
second table contains department.
I can select the highest salary in each department but
I would like to select the name of the employee who makes the highest salary in each department.
Can you help me to make this query ?
Thanks,
Aur=E9lie
--=_NextPart_000_0016_01C37937.E00063F0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Tom's Query will work provided that the =Salary field is declared as a money or decimal field. I have seen many =databases where (for portability and the capability of storing very large numbers) =that money field are defined as "float" columns. If this is the case =you should use a 'delta' value when comparing the salaries - because of computer =rounding errors you should never directly compare floats, doubles, anything with =a sliding decimal...so the comparison would be:
where abs(e.salary - (select max (e2.Salary) from Employees as =e2 where
= e2.Dept =3D d.Dept) ) < 0.0001
Bruce Carson
Director of Technology
Edgewater =Technology
"Tom Moreau" = wrote in message news:%23QjHCdVeDHA.=2680@.TK2MSFTNGP11.phx.gbl...
Try:

select
d.DeptName
, e.EmployeeName
from
Depts as d
join
Employees =as e on e.Dept =3D d.Dept
where
e.Salary =3D
(
=select
= max (e2.Salary)
=from
= Employees as e2
=where
= e2.Dept =3D d.Dept
)
-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql

"Aur=E9lie" =wrote in message news:294501c37955$7b=0322d0$a601280a@.phx.gbl...Hello,I have 2 tables.one table contains employees with their =salary.second table contains department.I can select the highest salary in each department butI would like to select the name of the employee who =makes the highest salary in each department.Can you help me to =make this query ?Thanks,Aur=E9lie

--=_NextPart_000_0016_01C37937.E00063F0--|||This is a multi-part message in MIME format.
--=_NextPart_000_037D_01C37939.390DAC40
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Good point. I just assumed it was stored as money. When you assume, =...
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Bruce A. Carson" <bcarson@.asgoth.com> wrote in message =news:eBBfolVeDHA.3576@.tk2msftngp13.phx.gbl...
Tom's Query will work provided that the Salary field is declared as a =money or decimal field. I have seen many databases where (for =portability and the capability of storing very large numbers) that money =field are defined as "float" columns. If this is the case you should =use a 'delta' value when comparing the salaries - because of computer =rounding errors you should never directly compare floats, doubles, =anything with a sliding decimal...so the comparison would be:
where abs(e.salary - (select max (e2.Salary) from Employees as e2 where e2.Dept =3D d.Dept) ) < 0.0001
Bruce Carson
Director of Technology
Edgewater Technology
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:%23QjHCdVeDHA.2680@.TK2MSFTNGP11.phx.gbl...
Try:
select
d.DeptName
, e.EmployeeName
from
Depts as d
join
Employees as e on e.Dept =3D d.Dept
where
e.Salary =3D
(
select
max (e2.Salary)
from
Employees as e2
where
e2.Dept =3D d.Dept
)
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Aur=E9lie" <av@.lbn.fr> wrote in message =news:294501c37955$7b0322d0$a601280a@.phx.gbl...
Hello,
I have 2 tables.
one table contains employees with their salary.
second table contains department.
I can select the highest salary in each department but
I would like to select the name of the employee who makes the highest salary in each department.
Can you help me to make this query ?
Thanks,
Aur=E9lie
--=_NextPart_000_037D_01C37939.390DAC40
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Good point. I just assumed it =was stored as money. When you assume, ...
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Bruce A. Carson" wrote in =message news:eBBfolVeDHA.3576=@.tk2msftngp13.phx.gbl...
Tom's Query will work provided that the =Salary field is declared as a money or decimal field. I have seen many =databases where (for portability and the capability of storing very large numbers) =that money field are defined as "float" columns. If this is the case =you should use a 'delta' value when comparing the salaries - because of computer =rounding errors you should never directly compare floats, doubles, anything with =a sliding decimal...so the comparison would be:
where abs(e.salary - (select max (e2.Salary) from Employees as =e2 where = e2.Dept =3D d.Dept) ) < 0.0001
Bruce Carson
Director of Technology
Edgewater =Technology
"Tom Moreau" = wrote in message news:%23QjHCdVeDHA.=2680@.TK2MSFTNGP11.phx.gbl...
Try:

select
d.DeptName
, e.EmployeeName
from
Depts as d
join
Employees =as e on e.Dept =3D d.Dept
where
e.Salary =3D
(
=select
= max (e2.Salary)
=from
= Employees as e2
=where
= e2.Dept =3D d.Dept
)
-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql

"Aur=E9lie" =wrote in message news:294501c37955$7b=0322d0$a601280a@.phx.gbl...Hello,I have 2 tables.one table contains employees with their =salary.second table contains department.I can select the highest salary in each department butI would like to select the name of the employee who =makes the highest salary in each department.Can you help me to =make this query ?Thanks,Aur=E9lie

--=_NextPart_000_037D_01C37939.390DAC40--

Monday, March 26, 2012

Query with MAX Date

I have the following situation:
The name of a product can change with time. These changes are stored in a
table with 3 columns: Product_id, Date and ProductName with Product_id and
Date forming the Primary Key.
I want to run a query that returns the product_id and each product's latest
name. For the life of me, I can't get my head rould what such a query would
look like. I can get a query that gives me just the product_id and the Max
of Date with a group by on the Product_id but when I introduce the
ProductName, it returns ALL names.
I have a gut feeling that there may be a subquery involved, or am I barking
up the wrong tree?
Any help much appreciated.
PeteYou can either use a subquery:
SELECT Product_id, ProductName
FROM Your_table t1
WHERE Date = (SELECT MAX(Date) FROM Your_table t2 WHERE t1.Product_id =
t2.Product_id)
or a derived table:
SELECT t1.Product_id, t1.ProductName
FROM Your_table t1
INNER JOIN
(SELECT Product_id, MAX(Date) AS max_date
FROM Your_table
GROUP BY Product_id) t2
ON t1.Product_id = t2.Product_id
AND t1.Date = t2.max_date
Jacco Schalkwijk
SQL Server MVP
"Italian Pete" <ItalianPete@.discussions.microsoft.com> wrote in message
news:4847B525-302C-4354-99EE-8165C56B60D9@.microsoft.com...
>I have the following situation:
> The name of a product can change with time. These changes are stored in a
> table with 3 columns: Product_id, Date and ProductName with Product_id and
> Date forming the Primary Key.
> I want to run a query that returns the product_id and each product's
> latest
> name. For the life of me, I can't get my head rould what such a query
> would
> look like. I can get a query that gives me just the product_id and the
> Max
> of Date with a group by on the Product_id but when I introduce the
> ProductName, it returns ALL names.
> I have a gut feeling that there may be a subquery involved, or am I
> barking
> up the wrong tree?
> Any help much appreciated.
> Pete|||First, please change your column name 'Date' to something more meaningful
and something that doesn't use a reserved word. Also, you should be
consistent in your column naming. Why does Product_id have an underscore,
but ProductName not? Finally, in the future, please post DDL, sample data,
and desired results. See http://www.aspfaq.com/5006
In the meantime, you can try this:
SELECT o.Product_id, i.MaxDate, o.ProductName
FROM Products o
INNER JOIN
(
SELECT Product_id, MaxDate = MAX([Date])
FROM Products
GROUP BY Product_id
) i
ON o.Product_id = i.Product_id
AND o.[Date] = i.MaxDate
http://www.aspfaq.com/
(Reverse address to reply.)
"Italian Pete" <ItalianPete@.discussions.microsoft.com> wrote in message
news:4847B525-302C-4354-99EE-8165C56B60D9@.microsoft.com...
> I have the following situation:
> The name of a product can change with time. These changes are stored in a
> table with 3 columns: Product_id, Date and ProductName with Product_id and
> Date forming the Primary Key.
> I want to run a query that returns the product_id and each product's
latest
> name. For the life of me, I can't get my head rould what such a query
would
> look like. I can get a query that gives me just the product_id and the
Max
> of Date with a group by on the Product_id but when I introduce the
> ProductName, it returns ALL names.
> I have a gut feeling that there may be a subquery involved, or am I
barking
> up the wrong tree?
> Any help much appreciated.
> Pete|||Here is a solution based on guesswork:
SELECT t1.product_id, t1.product_name
FROM tbl t1
WHERE ( SELECT MAX( t2.dtcol )
FROM tbl t2
WHERE t2.product_id = t1.product_id ) = t1.dtcol ;
If this is not what you are looking for, refer to www.aspfaq.com/5006 and
provide required information.
Anith

Friday, March 9, 2012

Query to concatenate results from multiple rows

I have a database where comments are stored in a separate table where the comment is split into max 80 char lengths and stored in separate rows.

eg.

RecordID Comment
001 This is a comment and the nex
001 t bit of the comment appears o
001 n the next line.
002 This is the start of the next com
002 ment.

I need a SQL query that will put the text back together again.

Many thanks
MUHow do you determine which order the segments should be assembled? Can they be put together in random order, or is there a definite sequence?

Do you want a solution that is simple, but SQL dialect specific, or do you want a generic solution that will work with most/all SQL dialects?

Do you want a solution for a single ID, or does it need to be able to work for the entire table in a single operation?

-PatP|||Pat,
Thanks for the response.

There is a LineNum field in the table to order the comments by.

The solution only needs to work with SQLServer.

Ideally I am looking for a solution that produces an entire set of rows showing details from a master table with the comment appearing from this table as a single field with the RecordID being used as the join field.

MarkU|||Ok, if you need to process multiple rows in a single set operation (ie SELECT statement), the best answer I've got is:CREATE TABLE #phrog (
recordId CHAR(3)
, comment VARCHAR(80)
, lineNum INT)

INSERT INTO #phrog (recordID, comment, lineNum)
SELECT '001', 'This is a comment and the nex', 1
UNION ALL SELECT '001', 't bit of the comment appears o', 2
UNION ALL SELECT '001', 'n the next line.', 3
UNION ALL SELECT '002', 'This is the start of the next com', 1
UNION ALL SELECT '002', 'ment.', 2

SELECT a.recordID, a.comment + Coalesce(b.comment, '') + Coalesce(c.comment, '')
FROM #phrog AS a
LEFT JOIN #phrog AS b
ON (b.recordID = a.recordID
AND b.lineNum = (SELECT Min(z1.lineNum)
FROM #phrog AS z1
WHERE z1.recordID = a.recordID
AND a.lineNum < z1.lineNum))
LEFT JOIN #phrog AS c
ON (c.recordID = a.recordID
AND c.lineNum = (SELECT Min(z1.lineNum)
FROM #phrog AS z1
WHERE z1.recordID = a.recordID
AND b.lineNum < z1.lineNum))
WHERE a.lineNum = (SELECT Min(z0.lineNum)
FROM #phrog AS z0
WHERE z0.recordID = a.recordID)

DROP TABLE #phrogBe forewarned that this code raises the kludge factor of the universe significantly, but it does work.

-PatP|||Many thanks for your help - I will check this out.

What I don't quite understand is that since I don't know upfront how many lines of comments there may be or what is in them, how can I do the UNION statements?

I was hoping that there would be some form of the UNION statement where I could say UNION ALL comment WHERE recordId = n (or similar).

MarkU|||On second thought, lets apply a very "Oracle-ish" solution. You could also use:CREATE TABLE dbo.phrog (
recordId CHAR(3)
, comment VARCHAR(80)
, lineNum INT)

INSERT INTO dbo.phrog (recordID, comment, lineNum)
SELECT '001', 'This is a comment and the nex', 1
UNION ALL SELECT '001', 't bit of the comment appears o', 2
UNION ALL SELECT '001', 'n the next line.', 3
UNION ALL SELECT '002', 'This is the start of the next com', 1
UNION ALL SELECT '002', 'ment.', 2
GO

CREATE FUNCTION dbo.phrogComment(@.recordID CHAR(3))
RETURNS VARCHAR(8000) AS
BEGIN
DECLARE
@.c VARCHAR(8000)
, @.r VARCHAR(8000)

SET @.r = ''

DECLARE z CURSOR FOR SELECT
comment
FROM dbo.phrog
WHERE recordID = @.recordID
ORDER BY lineNum

OPEN z
FETCH z INTO @.c

WHILE 0 = @.@.fetch_status
BEGIN
SET @.r = @.r + @.c
FETCH z INTO @.c
END

CLOSE z
DEALLOCATE z

RETURN @.r
END
GO

SELECT a.recordID, dbo.PhrogComment(a.recordID)
FROM dbo.phrog AS a
GROUP BY a.recordID

DROP FUNCTION dbo.phrogComment
DROP TABLE dbo.phrogThis will grieviously disturb the relational purist (me included), but it will get the job done quickly and simply.

-PatP|||I tried the second bit of code on my own tables, and it almost works perfectly. The problem I have is that the concatenated field being returned is being truncated at 256 total characters/spaces, yet I need it to be larger.

I tried to use a CAST on the PhrogComment(a.ID), as well as changing the VARCHAR sizes for @.c and @.r and the RETURNS value, all to no avail.

Any suggestions on how I could tweak the code to make the result "larger"?

Thanks,

Mark|||'taint the SQL code what's cuttin' ya off. It's the client.

In Query Analyzer:

1) Press shift-control-o to bring up the Options window.
2) Click the results tab.
3) At the right edge, near the middle, type in whatever column width seems kozy but not extravagant.
4) Re-run your query for optimum viewing pleasure!

Sorry if I'm a bit punchy... Things could charitably be described as "interesting" today.

-PatP|||Praise God! I've been losing my mind for the last 24 hours (it's been - how did you say it? - "interesting" :-)

Thanks so much. I should've known to blame it on SQL Query Analyzer - I've had some queries not work (i.e., a query will return 0 rows and throw no errors) in the Analyzer yet the same query works (return the expected results) if cut and pasted into and then run as a stored procedure - go figure.

Then again, I'm an econ major so the problem is probably behind the keyboard...

Mark|||Are you just wanting to do this for one message at a time in your procedure or are you wanting to return several messages.

Wednesday, March 28, 2012

Query with MAX funtion

Monday, March 26, 2012

Query with MAX Date

Friday, March 9, 2012

Query to concatenate results from multiple rows

Query Syntax

Blog Archive

About Me