problem with group by and maximum

Hi guys,
i have the following table:

FIELD1 - FIELD2 - FIELD3
1 --- A --- 23
1 --- B --- 77 <<< i want to select this row
2 --- C --- 12
2 --- D --- 99 <<< and this one
2 --- E --- 17
3 ...

I need to select FIELD1 and FIELD2 where FIELD3 is a maximum of the group grouped by FIELD1!

If i leave out FIELD2 the following query works:
select FIELD1, max(FIELD3)
from TABLE
group by FIELD1

But i dont know how to get FIELD2 selected as well.

Any ideas?

Thanks in advance.
AlexSELECT *
FROM YourTable A
WHERE EXISTS
(
SELECT Date, Time, max(rev#)
FROM YourTable B
GROUP BY Date, Time
HAVING A.Date = B.Date AND A.Time = B.Time AND A.rev# = B.max(rev#))sql

problem with delete statement

Hi guys,

i need some help on this issue, i have a table with a nvarchar type column with primary key constraint. I have made a procedure which deletes record from this table using the primary key. My primary key is TxnID, and when i ran the following statement:

DELETE from mytable where TxnID = '119DA-117440520'

it deleted the record with the id '119DA-1174405208'

I dont want this to happen, i only want to delete records with the exact IDs that i provide. Can any one tell me how i should do that?

Thanks in advance.

sorry! i managed to fix it...actually the width was not defined properly which was causing problem

sorry again

Problem with dates

My dilema would probably be nothing to some of you guys but I'm still new to tsql. I have a bunch of info that I need to have ordered by date. I have tried the following:

ORDER BY Due_Date DESC

I have my dates returned in the format : dd/mm/yyyy

But it only orders the days not months or years.

heres the output:

28/02/2004
20/02/2004
19/02/2004
10/12/2003 // as you can see this one should be at the top
05/02/2004
05/02/2004
05/02/2004
13/02/2004
05/02/2004
01/02/2004

Any help would be well appreciatedIs your date field a datetime or a varchar? If a varchar, this is the reason you should not store dates as a varchar. You can try:

ORDER BY CONVERT(datetime,Due_Date) DESC

This presumes your SQL Server is set up to properly parse dates in that format.|||All of my fields are datetime.|||28/02/2004
20/02/2004
19/02/2004
10/12/2003 // as you can see this one should be at the top
05/02/2004
05/02/2004
05/02/2004
13/02/2004
05/02/2004
01/02/2004

... that's all out of order if you think it's the order by.

it's not even order bying anything.

Are you doing a group by?|||There is another post on this issue where more information was provided.view post 466200

Please do not cross-post!

Let's drop this post and concentrate our efforts on the other one since it provides a fuller picture of the problem.

Terri

problem with date time field ...

Hi guys,
I have an asp page that needs to show data based on date criteria.
Basically the user selects a date and the asp page should display all
records within that day.
The problem is that this field contains date and time.
My current query is as follows:
RS.Open "Select * from DB1.dbo.logs WHERE Date = '" &
Request.Form("date") & "'", dbConn, 1
How should I modify this query so that it ignores the time ?
Thanks in advance !
http://www.karaszi.com/SQLServer/info_datetime.asp
http://www.karaszi.com/SQLServer/inf...asp#Searching
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<zerbie45@.gmail.com> wrote in message news:1141037700.450486.299340@.p10g2000cwp.googlegr oups.com...
> Hi guys,
> I have an asp page that needs to show data based on date criteria.
> Basically the user selects a date and the asp page should display all
> records within that day.
> The problem is that this field contains date and time.
> My current query is as follows:
> RS.Open "Select * from DB1.dbo.logs WHERE Date = '" &
> Request.Form("date") & "'", dbConn, 1
> How should I modify this query so that it ignores the time ?
> Thanks in advance !
>
|||Hey Tibor,
thanks for your reply; I'm just starting using asp and sql.
Do you know a quick way to modify this query so that it skips the time
? Your links are a bit too advanced for me.
That would be VERY appreciated.
Thanks in advance.
|||To add to Tibor's response, you can use a parameterized query to improve
performance, security and mitigate the need for date formatting. Also,
consider using a fast-forward cursor instead of a keyset one unless you have
a specific reason to do otherwise.
Set command = CreateObject("ADODB.Command")
command.ActiveConnection = connection
command.CommandText = _
"Select * from DB1.dbo.logs WHERE Date >= ? AND Date < ? + 1"
Set dateParameter1 = command.CreateParameter( _
"@.dateParameter1", 7, 1)
command.Parameters.Append dateParameter1
dateParameter1.Value = Request.Form("date")
Set dateParameter2 = command.CreateParameter( _
"@.dateParameter2", 7, 1)
command.Parameters.Append dateParameter2
dateParameter2.Value = Request.Form("date")
Set RS = command.Execute()
Hope this helps.
Dan Guzman
SQL Server MVP
<zerbie45@.gmail.com> wrote in message
news:1141037700.450486.299340@.p10g2000cwp.googlegr oups.com...
> Hi guys,
> I have an asp page that needs to show data based on date criteria.
> Basically the user selects a date and the asp page should display all
> records within that day.
> The problem is that this field contains date and time.
> My current query is as follows:
> RS.Open "Select * from DB1.dbo.logs WHERE Date = '" &
> Request.Form("date") & "'", dbConn, 1
> How should I modify this query so that it ignores the time ?
> Thanks in advance !
>

problem with date time field ...

Hi guys,
I have an asp page that needs to show data based on date criteria.
Basically the user selects a date and the asp page should display all
records within that day.
The problem is that this field contains date and time.
My current query is as follows:
RS.Open "Select * from DB1.dbo.logs WHERE Date = '" &
Request.Form("date") & "'", dbConn, 1
How should I modify this query so that it ignores the time ?
Thanks in advance !http://www.karaszi.com/SQLServer/info_datetime.asp
http://www.karaszi.com/SQLServer/in...e.asp#Searching
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<zerbie45@.gmail.com> wrote in message news:1141037700.450486.299340@.p10g2000cwp.googlegroups
.com...
> Hi guys,
> I have an asp page that needs to show data based on date criteria.
> Basically the user selects a date and the asp page should display all
> records within that day.
> The problem is that this field contains date and time.
> My current query is as follows:
> RS.Open "Select * from DB1.dbo.logs WHERE Date = '" &
> Request.Form("date") & "'", dbConn, 1
> How should I modify this query so that it ignores the time ?
> Thanks in advance !
>|||Hey Tibor,
thanks for your reply; I'm just starting using asp and sql.
Do you know a quick way to modify this query so that it skips the time
? Your links are a bit too advanced for me.
That would be VERY appreciated.
Thanks in advance.|||To add to Tibor's response, you can use a parameterized query to improve
performance, security and mitigate the need for date formatting. Also,
consider using a fast-forward cursor instead of a keyset one unless you have
a specific reason to do otherwise.
Set command = CreateObject("ADODB.Command")
command.ActiveConnection = connection
command.CommandText = _
"Select * from DB1.dbo.logs WHERE Date >= ? AND Date < ? + 1"
Set dateParameter1 = command.CreateParameter( _
"@.dateParameter1", 7, 1)
command.Parameters.Append dateParameter1
dateParameter1.Value = Request.Form("date")
Set dateParameter2 = command.CreateParameter( _
"@.dateParameter2", 7, 1)
command.Parameters.Append dateParameter2
dateParameter2.Value = Request.Form("date")
Set RS = command.Execute()
Hope this helps.
Dan Guzman
SQL Server MVP
<zerbie45@.gmail.com> wrote in message
news:1141037700.450486.299340@.p10g2000cwp.googlegroups.com...
> Hi guys,
> I have an asp page that needs to show data based on date criteria.
> Basically the user selects a date and the asp page should display all
> records within that day.
> The problem is that this field contains date and time.
> My current query is as follows:
> RS.Open "Select * from DB1.dbo.logs WHERE Date = '" &
> Request.Form("date") & "'", dbConn, 1
> How should I modify this query so that it ignores the time ?
> Thanks in advance !
>

problem with date time field ...

Hi guys,
I have an asp page that needs to show data based on date criteria.
Basically the user selects a date and the asp page should display all
records within that day.
The problem is that this field contains date and time.
My current query is as follows:
RS.Open "Select * from DB1.dbo.logs WHERE Date = '" &
Request.Form("date") & "'", dbConn, 1
How should I modify this query so that it ignores the time ?
Thanks in advance !http://www.karaszi.com/SQLServer/info_datetime.asp
http://www.karaszi.com/SQLServer/info_datetime.asp#Searching
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<zerbie45@.gmail.com> wrote in message news:1141037700.450486.299340@.p10g2000cwp.googlegroups.com...
> Hi guys,
> I have an asp page that needs to show data based on date criteria.
> Basically the user selects a date and the asp page should display all
> records within that day.
> The problem is that this field contains date and time.
> My current query is as follows:
> RS.Open "Select * from DB1.dbo.logs WHERE Date = '" &
> Request.Form("date") & "'", dbConn, 1
> How should I modify this query so that it ignores the time ?
> Thanks in advance !
>|||Hey Tibor,
thanks for your reply; I'm just starting using asp and sql.
Do you know a quick way to modify this query so that it skips the time
? Your links are a bit too advanced for me.
That would be VERY appreciated.
Thanks in advance.|||To add to Tibor's response, you can use a parameterized query to improve
performance, security and mitigate the need for date formatting. Also,
consider using a fast-forward cursor instead of a keyset one unless you have
a specific reason to do otherwise.
Set command = CreateObject("ADODB.Command")
command.ActiveConnection = connection
command.CommandText = _
"Select * from DB1.dbo.logs WHERE Date >= ? AND Date < ? + 1"
Set dateParameter1 = command.CreateParameter( _
"@.dateParameter1", 7, 1)
command.Parameters.Append dateParameter1
dateParameter1.Value = Request.Form("date")
Set dateParameter2 = command.CreateParameter( _
"@.dateParameter2", 7, 1)
command.Parameters.Append dateParameter2
dateParameter2.Value = Request.Form("date")
Set RS = command.Execute()
--
Hope this helps.
Dan Guzman
SQL Server MVP
<zerbie45@.gmail.com> wrote in message
news:1141037700.450486.299340@.p10g2000cwp.googlegroups.com...
> Hi guys,
> I have an asp page that needs to show data based on date criteria.
> Basically the user selects a date and the asp page should display all
> records within that day.
> The problem is that this field contains date and time.
> My current query is as follows:
> RS.Open "Select * from DB1.dbo.logs WHERE Date = '" &
> Request.Form("date") & "'", dbConn, 1
> How should I modify this query so that it ignores the time ?
> Thanks in advance !
>