Help with SQL query please

I have a table that have 4 fields of interest, A, B, C and D (plus others
that are not of interest).
I am not interested in completely unique rows (based on the fields A,B,C &
D).
What I am interested in rows where the A, B & C fields are identical, but
within these identical "groups" the D field is different.
So, I though I'd write an SQL statement that uses a sub query.
The inner query would bring back all rows that are not unique (by using a
count > 1 statement) and the outer query would then get the details
The SQL I came up with is as follows.
The query is as follows:
---
select A, B, C, D
from X
where exists
(select A, B, C, count(D)
from X
group by A, B, C
having count(D) > 1)
order by A, B, C
---
However...when running the sub query in isolation and ordering the results,
I found that the lowest value of "A" returned was 3. But, when I run the
query as a whole, the first set of results returned have data for rows that
contain A values of 1 & 2.
Puzzled over this, but now need some help.
Thanks in advance if you can provide this...
GriffPlease ALWAYS post DDL with questions like this so that we don't have
to guess what your table looks like. It also helps to include some
sample data and show your required end result.
The problems with the query you posted seem to be twofold. First the
subquery isn't correlated. Second COUNT(D) will count >1 if there is
more than one row even if D is the same (non-null) in each case. That
would make sense if (A,B,C,D) is a key of this table but you didn't
actually specify a key (did I mention about the importance of including
DDL?).
Assuming (A,B,C,D) is not nullable (yes, the DDL would have told us
that too) you can do it with a correlated subquery:
SELECT x.a, x.b, x.c, x.d
FROM x
WHERE EXISTS
(SELECT *
FROM x AS z
WHERE x.a = z.a
AND x.b = z.b
AND x.c = x.c
AND x.d <> z.d)
ORDER BY x.a, x.b, x.c, x.d ;
or with a derived table:
SELECT x.a, x.b, x.c, x.d
FROM
(SELECT a, b, c
FROM x
GROUP BY a, b, c
HAVING MIN(d)<MAX(d)) AS z
JOIN x
ON x.a = z.a
AND x.b = z.b
AND x.c = x.c
ORDER BY x.a, x.b, x.c, x.d ;
(both untested)
Essential reading on the best way to post a problem here:
http://www.aspfaq.com/etiquette.asp?id=5006
Hope this helps.
David Portas
SQL Server MVP
--

Help with SQL Query

Hi Guys,

I need "little help" (in my books) with my SQL Query.

I have a table which lists the total transactions entered by each staff member.
All the transactions are saved with the staffID of the staff member and the date of the transaction.

Date Amount Staff
1/1/2005 12 STaff1
1/1/2005 2 STaff2
1/1/2005 1 STaff3
1/1/2005 5 STaff1

Now the problem is that I need to select All the transactions from the table grouped on the basis of data and staff member,

so essentially its should look like

Date Total Staff1 Staff2 Staff3
1/1/2005 24 12 11 1

Moreover the number of staff members can be anything, so basically I can't even use sub queries.

I have been working on this one for a while now, and still don't know what to do... any help will be greatly appreciated.

ThanxYou will need a dynamic pivot (cross-tab) function - something like:

http://www.sqlteam.com/item.asp?ItemID=2955|||hi ehorn,

Thanks for your help... worked like a charm.. :)

Cheers!

help with sql query

hello all,
I need some urgent help with a query.
basically I have a simple table (sql server 2005 database) with this
kind of data
user date
john 1 apr
mark 31 mar
paul 2 apr
john 30 mar
john 14 apr
paul 4 apr
I need to build a query that, for a given user, retrieves the user
with the latest date only (the date field is formatted in the table as
datetime), for example john 14 apr...or paul 4 apr....
I'm kind of stuck...any help is greatly appreciated!
thanks in advance!
zz
Here is one way:
;WITH RankedUsers
AS
(SELECT [user], [date],
ROW_NUMBER() OVER(
PARTITION BY [user]
ORDER BY [date] DESC) AS seq
FROM Foo)
SELECT [user], [date]
FROM RankedUsers
WHERE seq = 1;
HTH,
Plamen Ratchev
http://www.SQLStudio.com

Help With SQL Query

Hello,
With the following table how would I create a query that would return all
rows whos EndDate minus StartDate is more than 28 Days.
TableName: Customers
ID - Integer
CustomerID - VarChar
StartDate - Date
EndDate - Date
Table:
ID CustomerID StartDate EndDate
1 Chuck1 9/1/06 9/30/06
2 Mike1 8/25/06 9/15/06
3 Dinah 8/23/06 9/1/06
4 James 7/11/06 8/30/06
The Query Should Return:
ID CustomerID StartDate EndDate
1 Chuck1 9/1/06 9/30/06
4 James 7/11/06 8/30/06
Thanks,
Chuck
SELECT
ID
, CustomerID
, StartDate
, EndDate
FROM Customers
WHERE datediff( day, StartDate, EndDate ) > 28
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Charles A. Lackman" <Charles@.CreateItSoftware.net> wrote in message news:O6E$smQ2GHA.5048@.TK2MSFTNGP05.phx.gbl...
> Hello,
> With the following table how would I create a query that would return all
> rows whos EndDate minus StartDate is more than 28 Days.
> TableName: Customers
> ID - Integer
> CustomerID - VarChar
> StartDate - Date
> EndDate - Date
> Table:
> ID CustomerID StartDate EndDate
> 1 Chuck1 9/1/06 9/30/06
> 2 Mike1 8/25/06 9/15/06
> 3 Dinah 8/23/06 9/1/06
> 4 James 7/11/06 8/30/06
>
> The Query Should Return:
> ID CustomerID StartDate EndDate
> 1 Chuck1 9/1/06 9/30/06
> 4 James 7/11/06 8/30/06
>
> Thanks,
> Chuck
>
|||Thank You
Chuck
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:uHoF7yQ2GHA.3656@.TK2MSFTNGP04.phx.gbl...
SELECT
ID
, CustomerID
, StartDate
, EndDate
FROM Customers
WHERE datediff( day, StartDate, EndDate ) > 28
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Charles A. Lackman" <Charles@.CreateItSoftware.net> wrote in message
news:O6E$smQ2GHA.5048@.TK2MSFTNGP05.phx.gbl...
> Hello,
> With the following table how would I create a query that would return all
> rows whos EndDate minus StartDate is more than 28 Days.
> TableName: Customers
> ID - Integer
> CustomerID - VarChar
> StartDate - Date
> EndDate - Date
> Table:
> ID CustomerID StartDate EndDate
> 1 Chuck1 9/1/06 9/30/06
> 2 Mike1 8/25/06 9/15/06
> 3 Dinah 8/23/06 9/1/06
> 4 James 7/11/06 8/30/06
>
> The Query Should Return:
> ID CustomerID StartDate EndDate
> 1 Chuck1 9/1/06 9/30/06
> 4 James 7/11/06 8/30/06
>
> Thanks,
> Chuck
>

Help with SQL Query

Hey all,
My abridged table structure is:
UNQ Number Student ID Date Attendance
1 1 1-feb-05 Y
2 2 2-feb-05 N
3 3 3-feb-05 Y
4 4 4-feb-05 C
5 1 .
6 2 .
7 3
8 4
9 1
10 2
11 3
12 4
13 1
14 2
15 3
16 4
and so on:
I need an output like (between two dates):
Student ID Present Absent Cancelled
1 10 4 3
These numbers are just examples.
I know I can write a query like:
select a.[student id],count(distinct a.[unique number]) from attendancetable
as a where a.[attendance]='Y' and a.[date]>='01-feb-05' and
a.[date]<='01-feb-05' group by a.[student id]
to get a result like:
Student ID Present
1 10
But, how can I get all the three information in one output?
Thank you.Try the following.
SELECT StudentID,
count(CASE Attendance WHEN 'Y' THEN 1 ELSE 0 END) AS 'Present',
count(CASE Attendance WHEN 'N' THEN 1 ELSE 0 END) AS 'Absent',
count(CASE Attendance WHEN C' THEN 1 ELSE 0 END) AS 'Cancelled'
FROM Attendance_Table
WHERE Date>= YourStartDate and Date <= YourEnddate
GROUP BY StudentID
GO
Gavin|||Thanks plenty!
A little modification:
SELECT distinct [Student ID],
count(CASE WHEN attendance='Y' THEN 1 ELSE null END) AS 'Present',
count(CASE WHEN attendance='N' THEN 1 ELSE null END) AS 'Absent',
count(CASE when Attendance='C' THEN 1 ELSE null END) AS 'Cancelled'
FROM StudentScheduleAttendanceDetails
WHERE Date>= '01-jan-03' and Date <= '01-feb-06' GROUP BY [Student ID] order
by [student id]
GO
did it. Thanks again!
"celtic_kiwi" <gavin.jolly@.gmail.com> wrote in message
news:1107658677.037863.312350@.f14g2000cwb.googlegroups.com...
> Try the following.
> SELECT StudentID,
> count(CASE Attendance WHEN 'Y' THEN 1 ELSE 0 END) AS 'Present',
> count(CASE Attendance WHEN 'N' THEN 1 ELSE 0 END) AS 'Absent',
> count(CASE Attendance WHEN C' THEN 1 ELSE 0 END) AS 'Cancelled'
> FROM Attendance_Table
> WHERE Date>= YourStartDate and Date <= YourEnddate
> GROUP BY StudentID
> GO
> --
> Gavin
>|||One other thing:
Is it possible to show Present / Total as a column ?
SELECT distinct a.[Student ID],b.[First Name],B.[Last Name],b.[E-Mail],
count(CASE WHEN a.[attendance]='Y' THEN 1 ELSE null END) AS 'Present',
count(CASE WHEN a.[attendance]='N' THEN 1 ELSE null END) AS 'Absent',
count(CASE when a.[Attendance]='C' THEN 1 ELSE null END) AS 'Cancelled',
count(CASE when a.[Attendance] = '' then 1 else null END) as 'Future',
count(CASE when a.[Attendance] like '%' then 1 else null END) as 'Total'
FROM StudentScheduleAttendanceDetails as a,StudentPersonalDetails as B
WHERE Date>= '01-jan-01' and
Date <= '01-jan-08'
and a.[student id]<>0 and b.[student id]=a.[student id]
and a.[student id] like '%'
GROUP BY a.[Student ID],b.[first name],b.[last name],b.[e-mail] order by
a.[student id]
somthing like:
SELECT distinct a.[Student ID],b.[First Name],B.[Last Name],b.[E-Mail],
count(CASE WHEN a.[attendance]='Y' THEN 1 ELSE null END) AS 'Present',
count(CASE WHEN a.[attendance]='N' THEN 1 ELSE null END) AS 'Absent',
count(CASE when a.[Attendance]='C' THEN 1 ELSE null END) AS 'Cancelled',
count(CASE when a.[Attendance] = '' then 1 else null END) as 'Future',
count(CASE when a.[Attendance] like '%' then 1 else null END) as 'Total'
(Present / Total) * 100 as "Present Ratio"
FROM StudentScheduleAttendanceDetails as a,StudentPersonalDetails as B
WHERE Date>= '01-jan-01' and
Date <= '01-jan-08'
and a.[student id]<>0 and b.[student id]=a.[student id]
and a.[student id] like '%'
GROUP BY a.[Student ID],b.[first name],b.[last name],b.[e-mail] order by
a.[student id]
So, I get something like
1011 A B a@.b.com 10 20 30 5 65 15.3
Can you please tell me if this can be done?
Thank you
Vince
"Vince" <nmvkPLEASERMVTHIS@.vsnl.net> wrote in message
news:uC63Lr$CFHA.2620@.tk2msftngp13.phx.gbl...
> Thanks plenty!
> A little modification:
> SELECT distinct [Student ID],
> count(CASE WHEN attendance='Y' THEN 1 ELSE null END) AS 'Present',
> count(CASE WHEN attendance='N' THEN 1 ELSE null END) AS 'Absent',
> count(CASE when Attendance='C' THEN 1 ELSE null END) AS 'Cancelled'
> FROM StudentScheduleAttendanceDetails
> WHERE Date>= '01-jan-03' and Date <= '01-feb-06' GROUP BY [Student ID]
order
> by [student id]
> GO
> did it. Thanks again!
> "celtic_kiwi" <gavin.jolly@.gmail.com> wrote in message
> news:1107658677.037863.312350@.f14g2000cwb.googlegroups.com...
>|||I think I have to only use a stored procedure, declare the variables, and
select the variable...
"Vince" <nmvkPLEASERMVTHIS@.vsnl.net> wrote in message
news:umGSdBADFHA.2600@.TK2MSFTNGP09.phx.gbl...
> One other thing:
> Is it possible to show Present / Total as a column ?
>
> SELECT distinct a.[Student ID],b.[First Name],B.[Last Name],b.[E-Mail],
> count(CASE WHEN a.[attendance]='Y' THEN 1 ELSE null END) AS 'Present',
> count(CASE WHEN a.[attendance]='N' THEN 1 ELSE null END) AS 'Absent',
> count(CASE when a.[Attendance]='C' THEN 1 ELSE null END) AS 'Cancelled',
> count(CASE when a.[Attendance] = '' then 1 else null END) as 'Future',
> count(CASE when a.[Attendance] like '%' then 1 else null END) as 'Total'
> FROM StudentScheduleAttendanceDetails as a,StudentPersonalDetails as B
> WHERE Date>= '01-jan-01' and
> Date <= '01-jan-08'
> and a.[student id]<>0 and b.[student id]=a.[student id]
> and a.[student id] like '%'
> GROUP BY a.[Student ID],b.[first name],b.[last name],b.[e-mail] order by
> a.[student id]
> somthing like:
> SELECT distinct a.[Student ID],b.[First Name],B.[Last Name],b.[E-Mail],
> count(CASE WHEN a.[attendance]='Y' THEN 1 ELSE null END) AS 'Present',
> count(CASE WHEN a.[attendance]='N' THEN 1 ELSE null END) AS 'Absent',
> count(CASE when a.[Attendance]='C' THEN 1 ELSE null END) AS 'Cancelled',
> count(CASE when a.[Attendance] = '' then 1 else null END) as 'Future',
> count(CASE when a.[Attendance] like '%' then 1 else null END) as 'Total'
> (Present / Total) * 100 as "Present Ratio"
>
> FROM StudentScheduleAttendanceDetails as a,StudentPersonalDetails as B
> WHERE Date>= '01-jan-01' and
> Date <= '01-jan-08'
> and a.[student id]<>0 and b.[student id]=a.[student id]
> and a.[student id] like '%'
> GROUP BY a.[Student ID],b.[first name],b.[last name],b.[e-mail] order by
> a.[student id]
> So, I get something like
> 1011 A B a@.b.com 10 20 30 5 65 15.3
> Can you please tell me if this can be done?
> Thank you
> Vince
>
> "Vince" <nmvkPLEASERMVTHIS@.vsnl.net> wrote in message
> news:uC63Lr$CFHA.2620@.tk2msftngp13.phx.gbl...
> order
>|||Glad I could help. I am new to this group and have a few questions open
so feel I should at least answer what I can.
Sorry, I should have left the function as SUM. The following will work
as well.
SELECT distinct [Student ID],
sum(CASE WHEN attendance='Y' THEN 1 ELSE 0 END) AS 'Present',
sum(CASE WHEN attendance='N' THEN 1 ELSE 0 END) AS 'Absent',
sum(CASE when Attendance='C' THEN 1 ELSE 0 END) AS 'Cancelled'
FROM StudentScheduleAttendanceDetails
WHERE Date>= '01-jan-03' and Date <= '01-feb-06' GROUP BY [Student ID]
order
by [student id]
GO|||Just guessing but this should work. But I have not tried doing
calculations on groups.
SELECT distinct a.[Student ID],b.[First Name],B.[Last Name],b.[E-Mail],
count(CASE WHEN a.[attendance]='Y' THEN 1 ELSE null END) AS 'Present',
count(CASE WHEN a.[attendance]='N' THEN 1 ELSE null END) AS 'Absent',
count(CASE WHEN a.[Attendance]='C' THEN 1 ELSE null END) AS
'Cancelled',
count(CASE WHEN a.[Attendance] = '' THEN 1 ELSE null END) as 'Future',
count(a.[Attendance]) as 'Total'),
count(CASE a.[attendance]
WHEN 'Y' THEN 1
WHEN 'N' THEN 1
WHEN 'C' THEN 1
ELSE null END) / count(a.[Attendance]) as 'Total')*100 as 'Present
Ratio'
FROM StudentScheduleAttendanceDetails as a,StudentPersonalDetails as B
WHERE Date>= '01-jan-01' and
Date <= '01-jan-08' and
a.[student id]<>0 and
b.[student id]=a.[student id]
/* and a.[student id] like '%' NOT SURE WHAT THIS LINE IS FOR' */
GROUP BY a.[Student ID],b.[first name],b.[last name],b.[e-mail]
ORDER BY a.[student id]|||Thanks Gavin! Really appreciate your help.
"celtic_kiwi" <gavin.jolly@.gmail.com> wrote in message
news:1107664607.420207.136340@.g14g2000cwa.googlegroups.com...
> Glad I could help. I am new to this group and have a few questions open
> so feel I should at least answer what I can.
> Sorry, I should have left the function as SUM. The following will work
> as well.
> SELECT distinct [Student ID],
> sum(CASE WHEN attendance='Y' THEN 1 ELSE 0 END) AS 'Present',
> sum(CASE WHEN attendance='N' THEN 1 ELSE 0 END) AS 'Absent',
> sum(CASE when Attendance='C' THEN 1 ELSE 0 END) AS 'Cancelled'
> FROM StudentScheduleAttendanceDetails
> WHERE Date>= '01-jan-03' and Date <= '01-feb-06' GROUP BY [Student ID]
> order
> by [student id]
> GO
>|||Again, thank you! It worked, I just had to cast as float...That [student id]
like '%' was to be like '3%' (all student ids beginning with 3)..
SELECT distinct a.[Student ID],b.[First Name],B.[Last Name],b.[E-Mail],
count(CASE WHEN a.[attendance]='Y' THEN 1 ELSE null END) AS 'Present',
count(CASE WHEN a.[attendance]='N' THEN 1 ELSE null END) AS 'Absent',
count(CASE WHEN a.[Attendance]='C' THEN 1 ELSE null END) AS 'Cancelled',
count(CASE WHEN a.[Attendance] = '' THEN 1 ELSE null END) as 'Future',
count(a.[Attendance]) as 'Total',
cast(cast(count(CASE when a.[attendance]='Y' THEN 1 else null end)as float)
/
count( a.[attendance])* 100 as decimal(4,0))
as 'Present Ratio (%)'
FROM StudentScheduleAttendanceDetails as a,StudentPersonalDetails as B
WHERE Date>= '01-jan-01' and
Date <= '01-jan-08' and
a.[student id]<>0 and
b.[student id]=a.[student id]
GROUP BY a.[Student ID],b.[first name],b.[last name],b.[e-mail]
ORDER BY a.[student id]
Again, thanks for your help.
"celtic_kiwi" <gavin.jolly@.gmail.com> wrote in message
news:1107666089.497943.221340@.g14g2000cwa.googlegroups.com...
> Just guessing but this should work. But I have not tried doing
> calculations on groups.
> SELECT distinct a.[Student ID],b.[First Name],B.[Last Name],b.[E-Mail],
> count(CASE WHEN a.[attendance]='Y' THEN 1 ELSE null END) AS 'Present',
> count(CASE WHEN a.[attendance]='N' THEN 1 ELSE null END) AS 'Absent',
> count(CASE WHEN a.[Attendance]='C' THEN 1 ELSE null END) AS
> 'Cancelled',
> count(CASE WHEN a.[Attendance] = '' THEN 1 ELSE null END) as 'Future',
> count(a.[Attendance]) as 'Total'),
> count(CASE a.[attendance]
> WHEN 'Y' THEN 1
> WHEN 'N' THEN 1
> WHEN 'C' THEN 1
> ELSE null END) / count(a.[Attendance]) as 'Total')*100 as 'Present
> Ratio'
> FROM StudentScheduleAttendanceDetails as a,StudentPersonalDetails as B
> WHERE Date>= '01-jan-01' and
> Date <= '01-jan-08' and
> a.[student id]<>0 and
> b.[student id]=a.[student id]
> /* and a.[student id] like '%' NOT SURE WHAT THIS LINE IS FOR' */
> GROUP BY a.[Student ID],b.[first name],b.[last name],b.[e-mail]
> ORDER BY a.[student id]
>

Help With SQL Query

Hello,
With the following table how would I create a query that would return all
rows whos EndDate minus StartDate is more than 28 Days.
TableName: Customers
ID - Integer
CustomerID - VarChar
StartDate - Date
EndDate - Date
Table:
ID CustomerID StartDate EndDate
1 Chuck1 9/1/06 9/30/06
2 Mike1 8/25/06 9/15/06
3 Dinah 8/23/06 9/1/06
4 James 7/11/06 8/30/06
The Query Should Return:
ID CustomerID StartDate EndDate
1 Chuck1 9/1/06 9/30/06
4 James 7/11/06 8/30/06
Thanks,
ChuckSELECT
ID
, CustomerID
, StartDate
, EndDate
FROM Customers
WHERE datediff( day, StartDate, EndDate ) > 28
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Charles A. Lackman" <Charles@.CreateItSoftware.net> wrote in message news:O6E$smQ2GHA.5048@.T
K2MSFTNGP05.phx.gbl...
> Hello,
>
> With the following table how would I create a query that would return all
> rows whos EndDate minus StartDate is more than 28 Days.
>
> TableName: Customers
> ID - Integer
> CustomerID - VarChar
> StartDate - Date
> EndDate - Date
>
> Table:
>
> ID CustomerID StartDate EndDate
> 1 Chuck1 9/1/06 9/30/06
> 2 Mike1 8/25/06 9/15/06
> 3 Dinah 8/23/06 9/1/06
> 4 James 7/11/06 8/30/06
>
>
> The Query Should Return:
>
> ID CustomerID StartDate EndDate
> 1 Chuck1 9/1/06 9/30/06
> 4 James 7/11/06 8/30/06
>
>
> Thanks,
>
> Chuck
>
>|||Thank You
Chuck
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:uHoF7yQ2GHA.3656@.TK2MSFTNGP04.phx.gbl...
SELECT
ID
, CustomerID
, StartDate
, EndDate
FROM Customers
WHERE datediff( day, StartDate, EndDate ) > 28
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Charles A. Lackman" <Charles@.CreateItSoftware.net> wrote in message
news:O6E$smQ2GHA.5048@.TK2MSFTNGP05.phx.gbl...
> Hello,
> With the following table how would I create a query that would return all
> rows whos EndDate minus StartDate is more than 28 Days.
> TableName: Customers
> ID - Integer
> CustomerID - VarChar
> StartDate - Date
> EndDate - Date
> Table:
> ID CustomerID StartDate EndDate
> 1 Chuck1 9/1/06 9/30/06
> 2 Mike1 8/25/06 9/15/06
> 3 Dinah 8/23/06 9/1/06
> 4 James 7/11/06 8/30/06
>
> The Query Should Return:
> ID CustomerID StartDate EndDate
> 1 Chuck1 9/1/06 9/30/06
> 4 James 7/11/06 8/30/06
>
> Thanks,
> Chuck
>

Help with SQL query

Hi, I have a table like the following fields:

TradeDate Item Price

Now, suppose I want to do an update like this: every price
corresponding to a date higher than '30 Sep 2005' will be reset to the
latest price in that item. Can I do something like the following

UPDATE Table t SET Price = (SELECT TOP 1 Price FROM Table q WHERE
q.Item = t.Item ORDER BY TradeDate DESC) WHERE t.TradeDate > '30 Sep
2005'

or is there a better way?

Thank you very much.

BrunoHi Bruno,

Perfect :-D Looks good to me.

HTH, Jens Suessmeyer

Help with SQL Query

I need help building the following query..

My table has the following schema: eventID, typeID

Sample Rows:

1,1
1,2
1,3
2,1
3,2
3,2
4,3
4,4
5,2

I want to be able to query for all eventID's such that type = 2 and
type <> 1. So the result should be

3,2
4,2

The result should NOT include 1,2 because eventID 1 is also "related"
to typeID 1 and 3.Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications. Your personal pseudo-code is wrong at many levels; did
you mean this?

CREATE TABLE EventSchedules
(event_id INTEGER NOT NULL
REFERENCES Events (event_id),
event_type INTEGER NOT NULL
CHECK (event_type > 0), --assumption
PRIMARY KEY (event_id, event_type)); --requirement!

INSERT INTO EventSchedules VALUES (1,1);
INSERT INTO EventSchedules VALUES (1,2);
INSERT INTO EventSchedules VALUES (1,3);
INSERT INTO EventSchedules VALUES (2,1);
INSERT INTO EventSchedules VALUES (3,2);
INSERT INTO EventSchedules VALUES (3,2);-- removed dup row!!
INSERT INTO EventSchedules VALUES (4,3);
INSERT INTO EventSchedules VALUES (4,4);
INSERT INTO EventSchedules VALUES (5,2);

A data element name like "type_id" makes no sense. Either it is an
identifier for a particular kind of entity or it is some kind of code
for an attribute. It cannot be both an attribute and an entity. You
might want to get a book on data modeling and the ISO-11179 Standards.

>> I want to be able to query for all event_id's such that event_type = 2 and event_type <> 1. <<

Here is one way.

SELECT event_id
FROM EventSchedules
GROUP BY event_id
HAVING MIN(event_type) > 1
AND MAX (CASE WHEN event_type <> 2 THEN 0 ELSE 2 END) = 2;

And I am sure that someone will come up with a self-join solution, too.|||b_naick@.yahoo.ca wrote:
> I need help building the following query..
> My table has the following schema: eventID, typeID
> Sample Rows:
> 1,1
> 1,2
> 1,3
> 2,1
> 3,2
> 3,2
> 4,3
> 4,4
> 5,2
> I want to be able to query for all eventID's such that type = 2 and
> type <> 1. So the result should be
> 3,2
> 4,2
> The result should NOT include 1,2 because eventID 1 is also "related"
> to typeID 1 and 3.

--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1

Shouldn't the last pair be 5,2, since there isn't any 4,2.

Try,

SELECT DISTINCT eventID, typeID
FROM t as t1
WHERE typeID = 2
AND eventID NOT IN (SELECT eventID FROM t WHERE typeID != 2)

Change table name "t" to your table's true name.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQpTZ5IechKqOuFEgEQKIrgCfeW81ytgRIXUnl//jAA0RU8zZwLQAoOPN
UBuYtqvs/JqhLjVuFIYYTzqF
=aqWw
--END PGP SIGNATURE--|||(b_naick@.yahoo.ca) writes:
> I need help building the following query..
> My table has the following schema: eventID, typeID
> Sample Rows:
> 1,1
> 1,2
> 1,3
> 2,1
> 3,2
> 3,2
> 4,3
> 4,4
> 5,2
> I want to be able to query for all eventID's such that type = 2 and
> type <> 1. So the result should be
> 3,2
> 4,2
> The result should NOT include 1,2 because eventID 1 is also "related"
> to typeID 1 and 3.

I assume that desired result is

3,2
5,2

Else there is something I don't understand at all.

This could be a good query:

SELECT *
FROM tbl a
WHERE a.type = 2
AND NOT EXISTS (SELECT *
FROM tbl b
WHERE a.eventID = b.eventID
AND EXISTS (SELECT *
FROM tbl c
WHERE c.eventID = b.eventID
ABD c.type = 1))

Since you did not include CREATE TABLE and INSERT statements, I
have not tested this.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

help with sql query

Dear all,

i have ' employee_Id ' column in table employeesIBM , and 'employee_Id' column in employeesSUN.

Now,

there are 4 records in employeesIBM.

and 10 records in employeesSUN.

*******What i want to achieve*****

i want to write a query which will display one column 'ALLemployees'

displaying 4 records of employeesIBM and 2 records in employeesSUN.

something like this >>>

employee_Id ******from employeesIBM table

1

2

3

4

employee_Id ******from employeesSUN table

10

11

want to write a query which will display something like this

ALLemployees

1

2

3

4

10

11

Please help me out with this.

Kris

Hi,

Try something like the following:

(
SELECT employee_id AS ALLemployees
FROM employeesIBM
)
UNION
(
SELECT employee_id
FROM employeesSUN
)

Not perfect, but it should get you the result set you're after. Note that if there are duplicate rows between tables and you don't want to filter out the duplicates then I believe you need to use UNION ALL instead of UNION

Hope that helps a bit, but sorry if it doesn't
|||

That does solves my prob.

Thanks,

Kris

sql

Help with SQL query

Hi,

Let's say I have a Customer table and an Order table. The latter is linked to the former through foreign key CustomerID. Now, I want to create a SQL statement which, given a CustomerID, returns the corresponding row in Customer table PLUS a field indicating the total number of orders this particular customer has. How can I achieve this with a single SQL statement?

Thanks in advance

--USE Northwind

SELECT Customers.CustomerID, SUM(Orders.OrderID) AS TotalOrders

FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID

GROUP BY Customers.CustomerID

|||Thanks, this is exactly what I want, except I need to change the aggregate function to Count instead of Sum.

help with sql query

Lets assume i have ' employee_Id ' column in table employeesIBM , and 'employee_Id' column in employeesSUN.

Now,

Lets once again assume,that there are 4 records in employeesIBM.

and 10 records in employeesSUN.

*******What i want to achieve*****

i want to write a query which will display one column 'ALLemployees'

displaying 4 records of employeesIBM and 2 records in employeesSUN.

something like this >>>

employee_Id ******from employeesIBM table

1

2

3

4

employee_Id ******from employeesSUN table

10

11

want to write a query which will display something like this

ALLemployees

1

2

3

4

10

11

Please help me out with this.

Kris

Kris, you've specified returning only 2 of 10 rows from your employeeSUN table without specifying the defining criteria causing only those two rows to be returned. My initial thought is to define a UNION query to produce this result, but a little more information will be required.

select employee_id as ALLemployees from employeesIBM

union all

select employee_id as ALLemployees from employeesSUN

where <some condition exists>

|||

Hi Allen,

Consider that im not so thorough with sql , But sure your reply has helped me a lot.

Thanks

Kris

Help with SQL Query

Given the following two tables below, I need help in writing a query that would retreive only 3 or less distinct values of BDesc from tbB table for every row found in tbA.

for example:

I expect result to be:

Aid Bdesc
100 1st Desc for 100
100 2nd Desc for 100
100 3rd Desc for 100
200 1st Desc for 200
200 2nd Desc for 200
200 3rd Desc for 200
300 1st Desc for 300
300 2nd Desc for 300
300 3rd Desc for 300
400 1st Desc for 400
500 1st Desc for 500
500 3rd Desc for 500

The tables are:

use tempdb

go

set nocount on

if exists (select name from sysobjects where name = 'TbA')

Drop table TbA

Create Table TbA ( Aid int )

Insert into TbA values(100)

Insert into TbA values(200)

Insert into TbA values(300)

Insert into TbA values(400)

--select * from TbA

if exists (select name from sysobjects where name = 'TbB')

Drop table TbB

Create Table TbB ( Bid int , BDesc varchar(50) )

INSERT INTO TbB Values(100, '1st Desc for 100')

INSERT INTO TbB Values(100, '2nd Desc for 100')

INSERT INTO TbB Values(100, '3rd Desc for 100')

INSERT INTO TbB Values(100, '3rd Desc for 100')

INSERT INTO TbB Values(200, '1st Desc for 200')

INSERT INTO TbB Values(200, '2nd Desc for 200')

INSERT INTO TbB Values(200, '3rd Desc for 200')

INSERT INTO TbB Values(200, '4th Desc for 200')

INSERT INTO TbB Values(200, '1st Desc for 200')

INSERT INTO TbB Values(300, '1st Desc for 300')

INSERT INTO TbB Values(300, '2nd Desc for 300')

INSERT INTO TbB Values(300, '3rd Desc for 300')

INSERT INTO TbB Values(300, '4th Desc for 300')

INSERT INTO TbB Values(400, '1st Desc for 400')

INSERT INTO TbB Values(400, '1st Desc for 400')

INSERT INTO TbB Values(500, '1st Desc for 500')

INSERT INTO TbB Values(500, '1st Desc for 500')

INSERT INTO TbB Values(500, '3rd Desc for 500')

--select * from TbB

Thanks for your help with this...

Here ya go

Code Snippet

selectdistinct TbB.*

from(selectdistinct aid from TbA)as TbA

innerjoin TbB

on TbA.Aid = TbB.Bid

and TbB.BDesc in

(selectdistincttop 3 BDesc from TbB where Bid = TbA.Aid orderby BDesc)

|||Thanks|||

How about if I wanted to get the result like this:

ColA ColB

100 1st Descfor 100, 2nd Descfor 100, 3rd Descfor 100

200 1st Descfor 200, 2nd Descfor 200, 3rd Descfor 200

300 1st Descfor 300, 2nd Descfor 300, 3rd Descfor 300

400 1st Descfor 400

500 1st Descfor 500, 3rd Descfor 500

|||hi, you can try using a udf

CREATE FUNCTION dbo.GetBDesc
(
@.AID int
)
RETURNS varchar(800)
AS
BEGIN
DECLARE @.BDesc varchar(100)
SET @.BDesc = ''
SELECT
@.BDesc = @.BDesc + BDesc + ','
FROM (SELECT DISTINCT TOP 3 * FROM TbB a WHERE a.BID = @.AID) b
WHERE BID = @.AID
ORDER BY
BDesc

IF @.BDesc <> '' SET @.BDesc = LEFT(@.BDesc, LEN(@.BDesc) - 1)

RETURN @.BDesc

END

GO

select *
, dbo.GetBDesc(AID)
from tba|||

If you use SQL Server 2005 you dont need a function...

Here the sample,

Code Snippet

Create Table #TableA(

Aid int );

Insert into #TableA values(100)

Insert into #TableA values(200)

Insert into #TableA values(300)

Insert into #TableA values(400)

Insert into #TableA values(500)

Create Table #TableB(

Bid int

,BDesc varchar(50)

)

INSERT INTO #TableB Values(100, '1st Desc for 100')

INSERT INTO #TableB Values(100, '2nd Desc for 100')

INSERT INTO #TableB Values(100, '3rd Desc for 100')

INSERT INTO #TableB Values(100, '3rd Desc for 100')

INSERT INTO #TableB Values(200, '1st Desc for 200')

INSERT INTO #TableB Values(200, '2nd Desc for 200')

INSERT INTO #TableB Values(200, '3rd Desc for 200')

INSERT INTO #TableB Values(200, '4th Desc for 200')

INSERT INTO #TableB Values(200, '1st Desc for 200')

INSERT INTO #TableB Values(300, '1st Desc for 300')

INSERT INTO #TableB Values(300, '2nd Desc for 300')

INSERT INTO #TableB Values(300, '3rd Desc for 300')

INSERT INTO #TableB Values(300, '4th Desc for 300')

INSERT INTO #TableB Values(400, '1st Desc for 400')

INSERT INTO #TableB Values(400, '1st Desc for 400')

INSERT INTO #TableB Values(500, '1st Desc for 500')

INSERT INTO #TableB Values(500, '1st Desc for 500')

INSERT INTO #TableB Values(500, '3rd Desc for 500')

;With DistinctData

as

(

Select Distinct A.Aid,B.BDesc from #TableA A Join #TableB B On A.Aid =B.Bid

),

RowData

as

(

Select Aid,Bdesc,Row_Number() Over(Partition By Aid Order By BDesc) RowID From DistinctData

)

/*

Select

Aid,

BDesc

From

RowData

Where

RowID <=3

*/

Select Distinct

Aid

,Substring((Select ',' + BDesc as [text()] From RowData Sub Where Sub.Aid=Main.Aid And Sub.RowId<=3 For XML Path(''), Elements),2,8000) as Descs

From

RowData Main

|||MG,

How about these two queries (both require SQL 2005)

select a.aid, b.bdesc
from tbA a
cross apply
(select distinct top (3) bdesc from tbB b where b.bid = a.aid) b
;

select a.aid as ColA, stuff((select distinct top (3) ', ' + bdesc from tbB b where b.bid = a.aid order by 1 for xml path('')),1,2,'') as ColB
from tbA a
;

The second one puts them into a single column for you.

Rob|||I should have mentioned that this is for SQL 2000 and for an OLTP environment. The procedure processes approx. 20,000 rows and right now it's using cursor logic which is slowing things down, so I was looking for ways to use set based processing. The function idea is good, but again its going to be row by row processing.|||

The function approach should be a significant improvement over any cursor processing.

Is there something we're not understanding about what you want to accomplish?

|||

Hi Rhamille Golimlim,

There is an issue when using "order by" during an aggregate concatenation query.

PRB: Execution Plan and Results of Aggregate Concatenation Queries Depend Upon Expression Location

http://support.microsoft.com/default.aspx/kb/287515

AMB

|||thanks for the tip hunchback. would it still show a different execution plan if we put the order by inside the subquery?|||

Hi Rhamille Golimlim,

If you put the "order by" clause inside the derived table, then how are you going to be sure that the result is sorted if the only way to asure a sorted resultset is using the "order by" clause in the statement that pull the data?. It is like sorting inside a view and not using "order by" clause when you pull from the view.

Concatenating row values in T-SQL

http://www.projectdmx.com/tsql/rowconcatenate.aspx

AMB

|||hi hunchback,

cool, would the xml path approach be the best work around for this scenario? or are there other alternatives or tsql hacks?

/rhamille

Help with SQL Query

Hello Everyone,

I need help writing this query. I have a table named "PMP" that stores up to 10 objective ID's:

Code Snippet

SELECT ObjectiveID1,ObjectiveID2,ObjectiveID3,ObjectiveID4,... ObjectiveID10

FROM PMP

WHERE PMPID = @.PMPID

I have a child table named "METRICS" that stores the details for each objective:

Code Snippet

SELECT ObjectiveID, TitleText, Description, KPIvalue

FROM METRICS

WHERE ObjectiveID IN (query the PMP table)

I need to write a query that will return all the Metrics that are used for a given PMPID. Any ideas how this can be done?

Thank You

My first suggestion is to correct a bad design mistake. You have a severly de-normalized table.

You most likely 'should' NOT have columns Objective1-Objective10.

You most likely would benefit from having a separate table for Objectives.

THEN issues such as this one would become MUCH easier to solve.

If you are using SQL 2005, you may find the UNPIVOT statement to be useful.

|||

Which version of SQL Server are you using?

AMB

|||

I am using SQL Server 2005 Express Edition

I understand why you would think this table is de-normalized, but it's the right structure for the solution. My organization allows a maximum of 10 objectives to be identified per request. I need to send my crystal report 1 record.

Can someone help me with the UNPIVOT query?

Thank You

|||

I figured it out:

SELECT * FROM COACHING.METRICS

WHERE OBJECTIVEID IN (

SELECT OBJECTIVEID

FROM

(SELECT RESULT1_OBJECTIVEID,

RESULT2_OBJECTIVEID,

RESULT3_OBJECTIVEID,

RESULT4_OBJECTIVEID,

RESULT5_OBJECTIVEID,

RESULT6_OBJECTIVEID,

RESULT7_OBJECTIVEID,

RESULT8_OBJECTIVEID,

RESULT9_OBJECTIVEID,

RESULT10_OBJECTIVEID

FROM COACHING.PMPFORMS

WHERE PMPID = 271 ) OBJ

UNPIVOT

(OBJECTIVEID FOR PMPID IN (RESULT1_OBJECTIVEID,

RESULT2_OBJECTIVEID,

RESULT3_OBJECTIVEID,

RESULT4_OBJECTIVEID,

RESULT5_OBJECTIVEID,

RESULT6_OBJECTIVEID,

RESULT7_OBJECTIVEID,

RESULT8_OBJECTIVEID,

RESULT9_OBJECTIVEID,

RESULT10_OBJECTIVEID)) VALS)

|||

Thanks Arnie Rowland and hunchback for your reponses

|||I'm glad we could point you in a direction that worked.sql

help with sql query

Lets assume i have ' employee_Id ' column in table employeesIBM , and 'employee_Id' column in employeesSUN.

Now,

Lets once again assume,that there are 4 records in employeesIBM.

and 10 records in employeesSUN.

*******What i want to achieve*****

i want to write a query which will display one column 'ALLemployees'

displaying 4 records of employeesIBM and 2 records in employeesSUN.

something like this >>>

employee_Id ******from employeesIBM table

1

2

3

4

employee_Id ******from employeesSUN table

10

11

want to write a query which will display something like this

ALLemployees

1

2

3

4

10

11

Please help me out with this.

Kris

Made another post about this at:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=424936&SiteID=1

PS. Prolly better if you only post in one place. For questions of this sort I think the Transact-SQL forum would be better suited.

Ta.

Help with SQL query

Please bare with me in trying to get my point across, I'm new to SQL and
would appreciate the help so much.
I have two table Table1 and Table2. There is a one to many relationship
between Table1 and Table2. Table2 has a list of widgets, I need to create a
query that would show the records from Table1 along with it's related
records and all non related records from Table2. So if I have 5 widgets and
only two have related records in Table1 results should be
Table1ID(2) fkTable2 Table2(widgetname1)
Table1ID(2) fkTable2 Table2(widgetname2)
null null Table2(widgetname3)
null null Table2(widgetname4)
null null Table2(widgetname5)
When I filter the query on another Table1ID, I would need the same sort of
result for each Table1ID.try this
SELECT TABLE1.COL1,TABLE1.COL2,TABLE2.COL1,TABLE2.COL2 FROM TABLE1 RIGHT
OUTER JOIN TABLE2 ON TABLE1.COL1 = TABLE2.COL1
--
Regards
R.D
--Knowledge gets doubled when shared
"Tim Harvey" wrote:

> Please bare with me in trying to get my point across, I'm new to SQL and
> would appreciate the help so much.
> I have two table Table1 and Table2. There is a one to many relationship
> between Table1 and Table2. Table2 has a list of widgets, I need to create
a
> query that would show the records from Table1 along with it's related
> records and all non related records from Table2. So if I have 5 widgets an
d
> only two have related records in Table1 results should be
> Table1ID(2) fkTable2 Table2(widgetname1)
> Table1ID(2) fkTable2 Table2(widgetname2)
> null null Table2(widgetname3)
> null null Table2(widgetname4)
> null null Table2(widgetname5)
> When I filter the query on another Table1ID, I would need the same sort of
> result for each Table1ID.
>
>
>

Help with SQL query

I have a ContactTypes table as follows:-
ContactTypeId int PK
ContactType varchar(50)
I have several different types of contact types (i.e. Invoice,
Business, Manager, Home, etc).
I have a Client table as follows:
ClienttId int PK
ClientName varchar(50)
I have a ContactAddresses table as follows:-
ContactAddressId int PK
ClientId
ContactTypeId
Address1
Address2
Town
Postcode
Can anyone tell me how i can write an SQL query to fetch all the
different types of contacts for a client, regardless if they exist or
not?
The query should return something like for a selected Client:-
Contact Type Address1 Address2 Town PostCode
Invoice 123 somewhere there AB12 C12
Business 456 overthere here CD34 E21
Manager
Home 987 here where D21 S12
So far i have come up with the following but it requires separate SQL
statements for each column. Can anyone recommend a way around this?
SELECT ContactType
,(SELECT Address1 FROM ContactAddresses CA WHERE ClientId = @.clientId
AND CA.ContactTypeId = CT.ContactTypeId)
,(SELECT Address2 FROM ContactAddresses CA WHERE ClientId = @.clientId
AND CA.ContactTypeId = CT.ContactTypeId)
,(SELECT Town FROM ContactAddresses CA WHERE ClientId = @.clientId AND
CA.ContactTypeId = CT.ContactTypeId)
,(SELECT PostCode FROM ContactAddresses CA WHERE ClientId = @.clientId
AND CA.ContactTypeId = CT.ContactTypeId)
FROM ContactTypes CT
Thanks in advance for any suggestions.
DanI think you want a cross join to get all possible combinations of client and
contact type, and an outer join to get the addresses that exist, while still
keeping the contact type/client combinations that do not.
select c.clientname
, b.ContactType
, a.Address1
, a.Address2
, a.Town
, a.PostCode
from ContactTypeId b
cross join Client c
left outer join ContactAddresses a
on c.ClienttId = a.ClienttId
and c.ContactTypeID = b.ContactTypeID
<dan_williams@.newcross-nursing.com> wrote in message
news:1148503334.127146.20190@.i39g2000cwa.googlegroups.com...
> I have a ContactTypes table as follows:-
> ContactTypeId int PK
> ContactType varchar(50)
> I have several different types of contact types (i.e. Invoice,
> Business, Manager, Home, etc).
> I have a Client table as follows:
> ClienttId int PK
> ClientName varchar(50)
> I have a ContactAddresses table as follows:-
> ContactAddressId int PK
> ClientId
> ContactTypeId
> Address1
> Address2
> Town
> Postcode
>
> Can anyone tell me how i can write an SQL query to fetch all the
> different types of contacts for a client, regardless if they exist or
> not?
> The query should return something like for a selected Client:-
> Contact Type Address1 Address2 Town PostCode
> Invoice 123 somewhere there AB12 C12
> Business 456 overthere here CD34 E21
> Manager
> Home 987 here where D21 S12
> So far i have come up with the following but it requires separate SQL
> statements for each column. Can anyone recommend a way around this?
> SELECT ContactType
> ,(SELECT Address1 FROM ContactAddresses CA WHERE ClientId = @.clientId
> AND CA.ContactTypeId = CT.ContactTypeId)
> ,(SELECT Address2 FROM ContactAddresses CA WHERE ClientId = @.clientId
> AND CA.ContactTypeId = CT.ContactTypeId)
> ,(SELECT Town FROM ContactAddresses CA WHERE ClientId = @.clientId AND
> CA.ContactTypeId = CT.ContactTypeId)
> ,(SELECT PostCode FROM ContactAddresses CA WHERE ClientId = @.clientId
> AND CA.ContactTypeId = CT.ContactTypeId)
> FROM ContactTypes CT
> Thanks in advance for any suggestions.
> Dan
>

Help with sql query

Hi

I have a query which should do the following..

Bring up all records from customer table that

datelastvisited field is not during the last 3 months
or is null
AND has a region matching a user input value

OR

in a customercalls table with a one to many relationship on customerid

if nextcalldate is within the lst 3 months
or is null

i thought i had it working but its not..

below is the query that i thought was working before I tried adding the null criteria..

SELECT customers.*, customers.CustomerLastVisitDate, CustomerCalls.CustomerCallDateNext FROM customers INNER JOIN CustomerCalls ON customers.CustomerID = CustomerCalls.CustomerID WHERE ((([customers.customerregion])='" & Me.cboRegion & "') AND ([customers.CustomerLastVisitDate] Not Between Date() And DateAdd('m',-3,Date()))) AND ((CustomerCalls.CustomerCallDateNext) Between Date() And DateAdd('m',-3,Date())) OR (([customercalls.customercalldatenext])=Date());

can anyone help me?

I seem to have lsot the plot..

thanks
matselect customers.*
from customers
where customerregion = '" & Me.cboRegion & "'
and (
CustomerLastVisitDate is null
or CustomerLastVisitDate
Not Between Date()
and DateAdd('m',-3,Date())
)
union
select customers.*
from customers
inner
join CustomerCalls
on customers.CustomerID
= CustomerCalls.CustomerID
where CustomerCalls.CustomerCallDateNext is null
or CustomerCalls.CustomerCallDateNext
Between Date()
and DateAdd('m',-3,Date())|||Thanks very much..
Ur a star!

mat

Help with SQL Query

Hi all,

I have a table as follows:
ID1 ID2 Property1 Property2 Date
1 23 P1 P2 01/04/2004
1 24 P22 P3 02/04/2004
2 25 P13 P22 01/05/2004
2 26 P34 P76 31/01/2004

What I want to retrieve is this:
For each value of ID1, I want to retrieve ONE record from all the ones sharing the same ID1 value, and this record is the earlist record of these.

For example, if I have the data above, I want to retrieve the following result

1 23 P1 P2 01/04/2004
2 26 P34 P76 31/01/2004

Any ideas how to do this? I've tried various GROUP BY and JOINS but can't seem to get anywhere near :(

Thanks!There are 2 steps involved:

1) What is the earliest date per ID1?

select id1, min(date) from table group by id1;

2) Get the records where the id1 and date values are in the list generated by query 1:

select * from table
where (id1, date) in (select id1, min(date) from table group by id1);|||Originally posted by andrewst
There are 2 steps involved:

1) What is the earliest date per ID1?

select id1, min(date) from table group by id1;

2) Get the records where the id1 and date values are in the list generated by query 1:

select * from table
where (id1, date) in (select id1, min(date) from table group by id1); Just an observation, but this can produce more than one row per value of ID1 if there are "ties" with the lowest date value.

-PatP|||and whaddya bet the poster's database won't support the

... where (a,b) in (select x,y...)

structure

(what's that called, anyway? a row expression?)

as far as i know, only oracle does

yeah, it's in sql-92, but since when does every database vendor support sql-92, eh|||So what is the alternative? I guess maybe:

select *
from table,
(select id1, min(date) mindate from table group by id1) v
where t.id1 = v.id1
and t.date = v.mindate;

...or is in-line view support unusual too?|||unusual? kinda

i call that a derived table

mysql certainly would have trouble with it, eh|||alternatives? two

correlated subquery:select ID1
, ID2
, Property1
, Property2
, myDate
from myTable zzz
where myDate =
( select min(myDate)
from myTable
where ID1 = zzz.ID1 )
self-join:select t1.ID1
, t1.ID2
, t1.Property1
, t1.Property2
, t1.myDate
from myTable t1
inner
join myTable t2
on t1.ID1 = t2.ID1
group
by t1.ID1
, t1.ID2
, t1.Property1
, t1.Property2
, t1.myDate
having t1.myDate = min(t2.myDate)

help with sql query

hello all,
I need some urgent help with a query.
basically I have a simple table (sql server 2005 database) with this
kind of data
user date
john 1 apr
mark 31 mar
paul 2 apr
john 30 mar
john 14 apr
paul 4 apr
I need to build a query that, for a given user, retrieves the user
with the latest date only (the date field is formatted in the table as
datetime), for example john 14 apr...or paul 4 apr....
I'm kind of stuck...any help is greatly appreciated!
thanks in advance!
zzHere is one way:
;WITH RankedUsers
AS
(SELECT [user], [date],
ROW_NUMBER() OVER(
PARTITION BY [user]
ORDER BY [date] DESC) AS seq
FROM Foo)
SELECT [user], [date]
FROM RankedUsers
WHERE seq = 1;
HTH,
Plamen Ratchev
http://www.SQLStudio.com

Help With SQL Query

Hello,
With the following table how would I create a query that would return all
rows whos EndDate minus StartDate is more than 28 Days.
TableName: Customers
ID - Integer
CustomerID - VarChar
StartDate - Date
EndDate - Date
Table:
ID CustomerID StartDate EndDate
1 Chuck1 9/1/06 9/30/06
2 Mike1 8/25/06 9/15/06
3 Dinah 8/23/06 9/1/06
4 James 7/11/06 8/30/06
The Query Should Return:
ID CustomerID StartDate EndDate
1 Chuck1 9/1/06 9/30/06
4 James 7/11/06 8/30/06
Thanks,
ChuckThis is a multi-part message in MIME format.
--=_NextPart_000_086D_01C6D8D2.0BEDBF60
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
SELECT
ID
, CustomerID
, StartDate
, EndDate
FROM Customers
WHERE datediff( day, StartDate, EndDate ) > 28
-- Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous
"Charles A. Lackman" <Charles@.CreateItSoftware.net> wrote in message =news:O6E$smQ2GHA.5048@.TK2MSFTNGP05.phx.gbl...
> Hello,
> > With the following table how would I create a query that would return =all > rows whos EndDate minus StartDate is more than 28 Days.
> > TableName: Customers
> ID - Integer
> CustomerID - VarChar
> StartDate - Date
> EndDate - Date
> > Table:
> > ID CustomerID StartDate EndDate
> 1 Chuck1 9/1/06 9/30/06
> 2 Mike1 8/25/06 9/15/06
> 3 Dinah 8/23/06 9/1/06
> 4 James 7/11/06 8/30/06
> > > The Query Should Return:
> > ID CustomerID StartDate EndDate
> 1 Chuck1 9/1/06 9/30/06
> 4 James 7/11/06 8/30/06
> > > Thanks,
> > Chuck > >
--=_NextPart_000_086D_01C6D8D2.0BEDBF60
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

SELECT
=ID
, =CustomerID
, =StartDate
, =EndDate
FROM Customers
WHERE datediff( day, StartDate, =EndDate ) > 28
-- Arnie Rowland, =Ph.D.Westwood Consulting, Inc
Most good judgment comes from =experience. Most experience comes from bad judgment. - Anonymous
"Charles A. Lackman" wrote in message news:O6E$smQ2GHA.5048@.TK2MSFTNGP05.phx.gbl...> =Hello,> > With the following table how would I create a query that would =return all > rows whos EndDate minus StartDate is more than 28 =Days.> > TableName: Customers> ID - Integer> CustomerID - VarChar> StartDate - Date> EndDate - Date> > =Table:> > ID CustomerID StartDate EndDate> 1 Chuck1 &=nbsp; 9/1/06 =9/30/06> 2 Mike1 &n=bsp; 8/25/06 =9/15/06> 3 Dinah &n=bsp; 8/23/06 9/1/06> 4 James &n=bsp; 7/11/06 8/30/06> > > The Query Should Return:> => ID CustomerID StartDate EndDate> 1 Chuck1 &=nbsp; 9/1/06 =9/30/06> 4 James &n=bsp; 7/11/06 8/30/06> > > Thanks,> > Chuck => >

---

--=_NextPart_000_086D_01C6D8D2.0BEDBF60--|||Thank You
Chuck
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:uHoF7yQ2GHA.3656@.TK2MSFTNGP04.phx.gbl...
SELECT
ID
, CustomerID
, StartDate
, EndDate
FROM Customers
WHERE datediff( day, StartDate, EndDate ) > 28
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Charles A. Lackman" <Charles@.CreateItSoftware.net> wrote in message
news:O6E$smQ2GHA.5048@.TK2MSFTNGP05.phx.gbl...
> Hello,
> With the following table how would I create a query that would return all
> rows whos EndDate minus StartDate is more than 28 Days.
> TableName: Customers
> ID - Integer
> CustomerID - VarChar
> StartDate - Date
> EndDate - Date
> Table:
> ID CustomerID StartDate EndDate
> 1 Chuck1 9/1/06 9/30/06
> 2 Mike1 8/25/06 9/15/06
> 3 Dinah 8/23/06 9/1/06
> 4 James 7/11/06 8/30/06
>
> The Query Should Return:
> ID CustomerID StartDate EndDate
> 1 Chuck1 9/1/06 9/30/06
> 4 James 7/11/06 8/30/06
>
> Thanks,
> Chuck
>sql