Help with SQL Query

Dear group,
I need to create a stored procedure to return some data. The trick is the
data needs to be AGGREGATED with a SUM of commissions against each line for
each broker...
I have a table (TRADE) with the following data:
trade_id (PK), broker_id, tradeType_id, commission_amount, datestamp
1, 1, 1, 150, 13/06/2006
2, 2, 1, 100, 13/06/2006
3, 1, 1, 75, 14/06/2006
4, 1, 2, 165, 14/06/2006
5, 3, 1, 33.50, 14/06/2006
I want to display (for a DataTable to be used in a Crystal Report) a grid
where the headers will be:
Broker ID, Daily Total (where tradeType_id = 1), Daily Total (tradeType_id =
2), Sum Daily Total, Monthly Total (where tradeType_id = 1), Monthly Total
(where tradeType_id = 2), Sum Monthly Total.
So that the query, when run on (14/06/2006), will look like:
1, 75, 165, 240, 225, 165, 190
2, null, null, null, 100, null, 100
3, 33.50, null, 33.50, 33.50, null, 33.50
The concept here is that I have a table which contains trades that a broker
has made. Each trade has a commission_amount column and a datestamp. I need
to be able to produce a report which has daily totals for different trade
types, but where the data is AGGREGATED by broker_id. All the SQL I've been
coming up with has been a total mess.
Can anyone assist with the above problem?
Many thanks!
MikeLiddle,
I think the trickiest thing here is the grouping. Is the monthly
total the running total since the first of the month, or just the sum
or trade type 2 records for a broker on a given day?
You can pretty easily group by date, broker id, and trade type.
SELECT broker_id, tradetype_id, datestamp, SUM(commission_amount) AS
sumcom
FROM TRADE
GROUP BY broker_id, tradetype_id, datestamp
but the most straightforward way to get it into the format you want is
to do two subqueries and join them back together. But, what that looks
like will depend on whether you're looking for a running total or not.
Ion
Liddle Feesh wrote:
> Dear group,
> I need to create a stored procedure to return some data. The trick is the
> data needs to be AGGREGATED with a SUM of commissions against each line fo
r
> each broker...
> I have a table (TRADE) with the following data:
> trade_id (PK), broker_id, tradeType_id, commission_amount, datestamp
> 1, 1, 1, 150, 13/06/2006
> 2, 2, 1, 100, 13/06/2006
> 3, 1, 1, 75, 14/06/2006
> 4, 1, 2, 165, 14/06/2006
> 5, 3, 1, 33.50, 14/06/2006
> I want to display (for a DataTable to be used in a Crystal Report) a grid
> where the headers will be:
> Broker ID, Daily Total (where tradeType_id = 1), Daily Total (tradeType_id
=
> 2), Sum Daily Total, Monthly Total (where tradeType_id = 1), Monthly Total
> (where tradeType_id = 2), Sum Monthly Total.
> So that the query, when run on (14/06/2006), will look like:
> 1, 75, 165, 240, 225, 165, 190
> 2, null, null, null, 100, null, 100
> 3, 33.50, null, 33.50, 33.50, null, 33.50
> The concept here is that I have a table which contains trades that a broke
r
> has made. Each trade has a commission_amount column and a datestamp. I nee
d
> to be able to produce a report which has daily totals for different trade
> types, but where the data is AGGREGATED by broker_id. All the SQL I've bee
n
> coming up with has been a total mess.
> Can anyone assist with the above problem?
> Many thanks!
> Mike|||<ionFreeman@.gmail.com> wrote in message
news:1150320207.374658.305960@.f6g2000cwb.googlegroups.com...
> Liddle,
> I think the trickiest thing here is the grouping. Is the monthly
> total the running total since the first of the month, or just the sum
> or trade type 2 records for a broker on a given day?
There are only four aggregated calculated fields, those are the daily totals
for trade_Type 1 and 2 and the monthly totals for trade_Type 1 and 2. The
sum is just an addition of those two fields and can be calculated easily by
hand.

> You can pretty easily group by date, broker id, and trade type.
> SELECT broker_id, tradetype_id, datestamp, SUM(commission_amount) AS
> sumcom
> FROM TRADE
> GROUP BY broker_id, tradetype_id, datestamp
> but the most straightforward way to get it into the format you want is
> to do two subqueries and join them back together. But, what that looks
> like will depend on whether you're looking for a running total or not.
Cheers, Jon - that's close, but not quite right. I'm looking for a distinct
group, so that if there are only 2 broker_id's, there will be only two rows
and the SUM() data will be aggregated on those rows.
Thanks!sql

Help with SQL Group By please (results returned into / shown in C#.Net)!

Hi all - i'm trying to put together my first .Net web page (have switched from Dreamweaver to VWD - VWD keeps swapping my tab-indents for spaces, and none of the options stop it!).

Here's a table that i'm trying to query: ItemID | ReviewRating | ReviewRatingOutOf

As i'm sure you've guessed, it's a reviews table, where there can be several records with the same ItemID and different (or the same) ReviewRating and ReviewRatingOutOf's. As the reviews are collected from lots of sources, the ReviewRatingOutOf will change (one review might be 3/5, while the next, for the same ItemID, could be 8/10, etc). Now, what i'm trying to do is return a list of ItemID's ordered by their RATIO (which is the sum of each ItemID's ReviewRating's divided by the sum of each ItemID's ReviewRatingsOutOf's - in other words, average score). My first guess was this:

"SELECT DISTINCT ItemID FROM Reviews ORDER BY SUM(ReviewRating)/SUM(ReviewRatingOutOf)" - unfortunately that doesn't work (problems with the SUM aggregate functions, and overflow errors, whatever they are). Now, this string works: "SELECT ItemID FROM Reviews GROUP BY ItemID ORDER BY SUM(ReviewRating)" - right now, that just adds up the ReviewRatings, so an item with 10 reviews that only got awarded 1/5, 1/10, 1/8, etc (all 1's, therefore achieving a combined ReviewRating of 10 out of a very much higher ReviewRatingOutOf), would appear higher than an item with 1 review that got 5/5. Making the string into this: "SELECT ItemID FROM Reviews GROUP BY ItemID ORDER BY SUM(ReviewRating)/SUM(ReviewRatingOutOf)" (which is what I need), unfortunately gives me errors...

Anyone have any ideas? Is there possibly a way to simply read all the distinct ItemID's with SQL, then get the two SUM's for each ItemID, then calculate the ratio of the two SUM's, and stick the ItemID's and the ratio into some sort of array, and have C# order the array for me, based on the ratio? I'd appreciate an example of that if possible, as i'm a complete C# beginner :-)

Thanks in advance!

anyone?|||

SELECT ItemID

FROM Reviews

GROUP BY ItemID

ORDER BY SUM(ReviewRating)/SUM(ReviewRatingOutOf)

or this:

SELECT ItemID

FROM Reviews

GROUP BY ItemID

ORDER BY AVG(ReviewRating/ReviewRatingOutOf)

If ReviewRating is an integer, and ReviewRatingOutOf is an integer, then you should cast one to a float like:

SELECT ItemID

FROM Reviews

GROUP BY ItemID

ORDER BY AVG(CAST(ReviewRating AS float)/ReviewRatingOutOf)

otherwise you won't get what you expect because (int) / (int) will always return the floor of the result. So 1/2=0 1/8=0, 1/10=0, and the only way to score a non-zero result would be a perfect 5/5, 10/10, etc. By casting one to a float, then it the divide will return a float result. So (float)1/(int)2=(float)0.5

|||

Thanks for trying Motley - but all those methods give an 'overflow' error. I'm using Access 2003 by the way - just realised that may be important and that I hadn't mentioned it!

Any more help really appreciated!

|||Is there a record that ReviewRatingOutOf is 0?|||

Ahhh - yeah there is. Is there an easy way around that, or is it best to simply remove/change it? It's a review that didn't have an accompanying score (obviously!).

Thanks again for you help so far Motley.

|||

Think I got it working myself - here's my final code:

"SELECT ItemID FROM Reviews GROUP BY ItemID HAVING SUM(ReviewRatingOutOf) > 0 ORDER BY SUM(ReviewRating)/SUM(ReviewRatingOutOf) DESC"

That seems to give me exactly what I was after, with the highest-rated items arriving first. Unless you see something that'll cause problems, it's the perfect solution for me (that also allows for items with ReviewRatingOutOf = 0).

Thanks again for your help!

|||Just make sure that if both ReviewRating and ReviewRatingOutOf are both defined as an integer type (int,bigint,smallint,tinyint,bit) that you cast one or both to a float before the division or you'll get unexpected results.

HELP with SQL Date Function

Is there a Function that would just give me the DATE and not the DATE & TIME? I am trying to group by VRUServiceDate but since this is a smalldatetime data type, the grouping does not work by date because the time shows.

Thank you.

SELECT
VRUServiceDate AS [Service Date],
EmployeeID AS [Rep ID],
SUM(VRUDriveMiles) AS [Miles Traveled]
FROM WorkOrder
WHERE TagToPay=1 AND Paid=0 and vrudrivemiles <> 0
GROUP BY VRUServiceDate,EmployeeID
ORDER BY EmployeeID,VRUServiceDatei would use the CONVERT function --

SELECT
CONVERT(CHAR(10),VRUServiceDate,126) AS [Service Date],
EmployeeID AS [Rep ID],
SUM(VRUDriveMiles) AS [Miles Traveled]
FROM WorkOrder
WHERE TagToPay=1 AND Paid=0 and vrudrivemiles <> 0
GROUP BY CONVERT(CHAR(10),VRUServiceDate,126),EmployeeID
ORDER BY EmployeeID,CONVERT(CHAR(10),VRUServiceDate,126)

if the expression does not work in the ORDER BY, use the ordinal form instead -- ORDER BY 2,1

rudy
http://r937.com/|||It worked great but I had to change 126 to 101. Thanks!!

help with simple query using group by and where clause

have a table with sale_id, date, sales_person_id

i need to find out the sales_person_id's who did 1 sales every month
from jan 2003 and another query who did a sales every quarter.

How many sales person have atleast one sale every month (excluding prints) for either 2003, 2004, or 2005?

How many sales person had atleast 25 sales each year 2003-2005

It is not clear how you have the date specified in the table. And also what does excluding prints mean? Is there some other column that tracks this detail? If so, you need to include the condition in the query below:

SELECT COUNT(*) as NumSalesPerson

FROM (

SELECT year(s.date) as yr, s.sales_person_id

FROM Sales AS s

WHERE s.date >= '20030101' and s.date <'20060101'

GROUP BY year(s.date)

HAVING COUNT(*) >= @.n -- can be 1 or 25 or whatever

) AS s1

HELP with Running value totals

HI,
I have a table created. I need to have static fields.
The table has one group, where I use an IIF statement to point the values
into one of the three static fields. The reason I am using a table is that I
have to show months/loan programs with zero as well. The fixed static fields
are The loan types. I need to add a sum to the group footer so for each
month I can show the grand total of the three loan programs. Please help me
with this...I have been working on this for two days now and just can not
figure it out. I tried the following: =Sum(ReportItems!Textbox21.Value +
ReportItems!Guar_Dollar_Amt.Value + ReportItems!Textbox47.Value) but receive
an error
that states "The value expression for the textbox â'textbox31â' refers to the
report item â'Textbox21â'. Report item expressions can only refer to other
report items within the same grouping scope or a containing grouping scope."
Okay so here it is:
FY2003 FY2004 FY2005
Oct Loan1 =RunningValue( iif(Fields!Loan_type_Code.value = "Loan1" and
Fields!FCLYR.Value = 2002, CDbl(Fields!Guar_Dollar_Amount.Value), CDbl(0)),
Sum, Nothing)
Loan2 =RunningValue( iif(Fields!Loan_type_Code.value = "Loan2"
and Fields!FCLYR.Value = 2002, CDbl(Fields!Guar_Dollar_Amount.Value),
CDbl(0)), Sum, Nothing)
Loan3 =RunningValue( iif(Fields!Loan_type_Code.value = "Loan3" and Fields!FCLYR.Value = 2002, CDbl(Fields!Guar_Dollar_Amount.Value),
CDbl(0)), Sum, Nothing)All of the texboxes have to be in the same scope... (the same level in the
table etc...), and you probably need to supply the scope name ie the group
name etc.. You may even have to split up the sums ie
=Sum(Reportitems!textbox1.Value,"mygroup") +
sum(ReportItems!Textbox2.Value,"mygroup") ...etc
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Susan" wrote:
> HI,
> I have a table created. I need to have static fields.
> The table has one group, where I use an IIF statement to point the values
> into one of the three static fields. The reason I am using a table is that I
> have to show months/loan programs with zero as well. The fixed static fields
> are The loan types. I need to add a sum to the group footer so for each
> month I can show the grand total of the three loan programs. Please help me
> with this...I have been working on this for two days now and just can not
> figure it out. I tried the following: =Sum(ReportItems!Textbox21.Value +
> ReportItems!Guar_Dollar_Amt.Value + ReportItems!Textbox47.Value) but receive
> an error
> that states "The value expression for the textbox â'textbox31â' refers to the
> report item â'Textbox21â'. Report item expressions can only refer to other
> report items within the same grouping scope or a containing grouping scope."
> Okay so here it is:
> FY2003 FY2004 FY2005
> Oct Loan1 =RunningValue( iif(Fields!Loan_type_Code.value = "Loan1" and
> Fields!FCLYR.Value = 2002, CDbl(Fields!Guar_Dollar_Amount.Value), CDbl(0)),
> Sum, Nothing)
> Loan2 =RunningValue( iif(Fields!Loan_type_Code.value = "Loan2"
> and Fields!FCLYR.Value = 2002, CDbl(Fields!Guar_Dollar_Amount.Value),
> CDbl(0)), Sum, Nothing)
> Loan3 =RunningValue( iif(Fields!Loan_type_Code.value => "Loan3" and Fields!FCLYR.Value = 2002, CDbl(Fields!Guar_Dollar_Amount.Value),
> CDbl(0)), Sum, Nothing)|||This is exactly the problem that I'm having. I notice that there has been
no reply to this request in a month. Is that because there's no way to make
it work?
"Susan" <Susan@.discussions.microsoft.com> wrote in message
news:817F9F2D-3BE0-489A-8575-325DFE42CC20@.microsoft.com...
> HI,
> I have a table created. I need to have static fields.
> The table has one group, where I use an IIF statement to point the values
> into one of the three static fields. The reason I am using a table is
> that I
> have to show months/loan programs with zero as well. The fixed static
> fields
> are The loan types. I need to add a sum to the group footer so for each
> month I can show the grand total of the three loan programs. Please help
> me
> with this...I have been working on this for two days now and just can not
> figure it out. I tried the following: =Sum(ReportItems!Textbox21.Value +
> ReportItems!Guar_Dollar_Amt.Value + ReportItems!Textbox47.Value) but
> receive
> an error
> that states "The value expression for the textbox 'textbox31' refers to
> the
> report item 'Textbox21'. Report item expressions can only refer to other
> report items within the same grouping scope or a containing grouping
> scope."
> Okay so here it is:
> FY2003 FY2004 FY2005
> Oct Loan1 =RunningValue( iif(Fields!Loan_type_Code.value = "Loan1"
> and
> Fields!FCLYR.Value = 2002, CDbl(Fields!Guar_Dollar_Amount.Value),
> CDbl(0)),
> Sum, Nothing)
> Loan2 =RunningValue( iif(Fields!Loan_type_Code.value = "Loan2"
> and Fields!FCLYR.Value = 2002, CDbl(Fields!Guar_Dollar_Amount.Value),
> CDbl(0)), Sum, Nothing)
> Loan3 =RunningValue( iif(Fields!Loan_type_Code.value => "Loan3" and Fields!FCLYR.Value = 2002,
> CDbl(Fields!Guar_Dollar_Amount.Value),
> CDbl(0)), Sum, Nothing)

Help with Query ?

have two tables with the following strcuture :

Test Type
ID
GroupID
Type

Type Group
GroupID
Description

Now type group has lot of data in it, I want only
GroupID which has specific name
so I did....

SELECT * FROM
TypeGroup tg
WHERE tg.Description = 'Test'
OR tg.Description = 'Test1'

Now this gives me two groupID's so far its fine....

Now based on the groupID's I need to get all the types
from Test Type Table and I did something like this :

SELECT Type
FROM TypeGroup tg, TestType tt
WHERE tg.Description = 'Test'
OR tg.Description = 'Test1'
AND tg.GroupID = tt.GroupID

The above where its not happy, How can I perform to get
the desired results :

The result what I'm getting now is everything and some
repetition from TestType table...

Sample Data :

Type Group Table
1, Test
2, Test1
3, Test2
4, Test4
5, Test10

Test Type Table
1, 1, Something
2,1, Something else
3,1, Something different
4, 2, Very Different
5,3,Testing is good
6,3, Do More Testing<br. 7,4,Yield Better Results>
8,5, The better the results, better it is

From the Query I'm looking for is
Something,Somethng else, Something different, Very Different

Thanks a lot for the help.

First, you are using the 'old form' of JOINs. A JOIN should be in the new form for reliability. If I understand your question correctly, this may work for you:

Code Snippet

SET NOCOUNT ON

DECLARE @.TypeGroup table
( TypeGroupID int IDENTITY,
TypeDescrip varchar(20)
)

INSERT INTO @.TypeGroup VALUES ( 'Test' )
INSERT INTO @.TypeGroup VALUES ( 'Test1' )
INSERT INTO @.TypeGroup VALUES ( 'Test2' )
INSERT INTO @.TypeGroup VALUES ( 'Test4' )
INSERT INTO @.TypeGroup VALUES ( 'Test10' )

DECLARE @.TestType table
( TestTypeID int IDENTITY,
TypeGroupID int,
TestDescript varchar(50)
)

INSERT INTO @.TestType VALUES ( 1, 'Something' )
INSERT INTO @.TestType VALUES ( 1, 'Something else' )
INSERT INTO @.TestType VALUES ( 1, 'Something different' )
INSERT INTO @.TestType VALUES ( 2, 'Very Different' )
INSERT INTO @.TestType VALUES ( 2, 'Very Different' )
INSERT INTO @.TestType VALUES ( 3, 'Testing is good' )
INSERT INTO @.TestType VALUES ( 4, 'Testing is good' )
INSERT INTO @.TestType VALUES ( 3, 'Do More Testing' )
INSERT INTO @.TestType VALUES ( 4, 'Yield Better Results' )
INSERT INTO @.TestType VALUES ( 5, 'The better the results, better it is' )

SELECT DISTINCT tt.TestDescript
FROM @.TypeGroup tg
JOIN @.TestType tt
ON tg.TypeGroupID = tt.TypeGroupID
WHERE ( tg.TypeDescrip = 'Test'
OR tg.TypeDescrip = 'Test1'
)

help with query

this is my query
Select
fname
,Case
When Count(animal) >1 Then Convert(nvarchar(64), Count(animal))
Else animal
End
from tanimals
Group By fname, animal
create table tanimals
(
fname nvarchar(64),
animal nvarchar(64),
)
insert into tanimals (fname, animal) values ('James', 'cat')
insert into tanimals (fname, animal) values ('James', 'dog')
insert into tanimals (fname, animal) values ('Cal', 'dog')
insert into tanimals (fname, animal) values ('Cal', 'fish')
insert into tanimals (fname, animal) values ('Bret', 'fish')
insert into tanimals (fname, animal) values ('Kate', 'fish')
insert into tanimals (fname, animal) values ('Cal', 'turtle')
I'm trying to produce
fname animals
James 2
Cal 3
Kate fish
Bret fish
if the person have more that one animals to give me the number of
animals, but if they own just one to return the animal that they own.
ThanksSelect
fname
,Case
When Count(animal) >1 Then Convert(nvarchar(64), Count(animal))
Else MAX(animal) -- Use aggregate to satisfy Group By
End as Animals
from tanimals
Group By fname -- Not including animal
order by Animals
RLF
<vncntj@.hotmail.com> wrote in message
news:1174415339.746907.287300@.o5g2000hsb.googlegroups.com...
> this is my query
> Select
> fname
> ,Case
> When Count(animal) >1 Then Convert(nvarchar(64), Count(animal))
> Else animal
> End
> from tanimals
> Group By fname, animal
> create table tanimals
> (
> fname nvarchar(64),
> animal nvarchar(64),
> )
> insert into tanimals (fname, animal) values ('James', 'cat')
> insert into tanimals (fname, animal) values ('James', 'dog')
> insert into tanimals (fname, animal) values ('Cal', 'dog')
> insert into tanimals (fname, animal) values ('Cal', 'fish')
> insert into tanimals (fname, animal) values ('Bret', 'fish')
> insert into tanimals (fname, animal) values ('Kate', 'fish')
> insert into tanimals (fname, animal) values ('Cal', 'turtle')
>
> I'm trying to produce
> fname animals
> James 2
> Cal 3
> Kate fish
> Bret fish
> if the person have more that one animals to give me the number of
> animals, but if they own just one to return the animal that they own.
> Thanks
>|||Below are two options. Second requires SQL Server 2005:
SELECT fname, CAST(COUNT(*) AS varchar(30)) AS animals
FROM tanimals
GROUP BY fname
HAVING COUNT(*) > 1
UNION ALL
SELECT fname, animal
FROM tanimals
WHERE fname NOT IN
(
SELECT fname
FROM tanimals
GROUP BY fname
HAVING COUNT(*) > 1
);
WITH ganimals AS
(SELECT fname, CAST(COUNT(*) AS varchar(30)) AS animals
FROM tanimals
GROUP BY fname
HAVING COUNT(*) > 1)
SELECT fname, animal
FROM tanimals
WHERE fname NOT IN (SELECT fname FROM ganimals)
UNION
SELECT fname, animals FROM ganimals
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<vncntj@.hotmail.com> wrote in message news:1174415339.746907.287300@.o5g2000hsb.googlegroups.com...
> this is my query
> Select
> fname
> ,Case
> When Count(animal) >1 Then Convert(nvarchar(64), Count(animal))
> Else animal
> End
> from tanimals
> Group By fname, animal
> create table tanimals
> (
> fname nvarchar(64),
> animal nvarchar(64),
> )
> insert into tanimals (fname, animal) values ('James', 'cat')
> insert into tanimals (fname, animal) values ('James', 'dog')
> insert into tanimals (fname, animal) values ('Cal', 'dog')
> insert into tanimals (fname, animal) values ('Cal', 'fish')
> insert into tanimals (fname, animal) values ('Bret', 'fish')
> insert into tanimals (fname, animal) values ('Kate', 'fish')
> insert into tanimals (fname, animal) values ('Cal', 'turtle')
>
> I'm trying to produce
> fname animals
> James 2
> Cal 3
> Kate fish
> Bret fish
> if the person have more that one animals to give me the number of
> animals, but if they own just one to return the animal that they own.
> Thanks
>|||vncntj,
I believe this will work for you:
select
fname
, animal AnimalOrCount
from tanimals
where fname in
(
select fname
from tanimals
group by fname
having count(*) = 1
)
union all
select fname
, cast(count(*) as nvarchar(64)) AnimalOrCount
from tanimals
group by fname
having count(*) > 1
-- Bill
<vncntj@.hotmail.com> wrote in message
news:1174415339.746907.287300@.o5g2000hsb.googlegroups.com...
> this is my query
> Select
> fname
> ,Case
> When Count(animal) >1 Then Convert(nvarchar(64), Count(animal))
> Else animal
> End
> from tanimals
> Group By fname, animal
> create table tanimals
> (
> fname nvarchar(64),
> animal nvarchar(64),
> )
> insert into tanimals (fname, animal) values ('James', 'cat')
> insert into tanimals (fname, animal) values ('James', 'dog')
> insert into tanimals (fname, animal) values ('Cal', 'dog')
> insert into tanimals (fname, animal) values ('Cal', 'fish')
> insert into tanimals (fname, animal) values ('Bret', 'fish')
> insert into tanimals (fname, animal) values ('Kate', 'fish')
> insert into tanimals (fname, animal) values ('Cal', 'turtle')
>
> I'm trying to produce
> fname animals
> James 2
> Cal 3
> Kate fish
> Bret fish
> if the person have more that one animals to give me the number of
> animals, but if they own just one to return the animal that they own.
> Thanks
>|||> Else MAX(animal) -- Use aggregate to satisfy Group By
LOL... That is much more clever than my approach. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:eivaP9xaHHA.4888@.TK2MSFTNGP06.phx.gbl...
> Select
> fname
> ,Case
> When Count(animal) >1 Then Convert(nvarchar(64), Count(animal))
> Else MAX(animal) -- Use aggregate to satisfy Group By
> End as Animals
> from tanimals
> Group By fname -- Not including animal
> order by Animals
> RLF
> <vncntj@.hotmail.com> wrote in message news:1174415339.746907.287300@.o5g2000hsb.googlegroups.com...
>> this is my query
>> Select
>> fname
>> ,Case
>> When Count(animal) >1 Then Convert(nvarchar(64), Count(animal))
>> Else animal
>> End
>> from tanimals
>> Group By fname, animal
>> create table tanimals
>> (
>> fname nvarchar(64),
>> animal nvarchar(64),
>> )
>> insert into tanimals (fname, animal) values ('James', 'cat')
>> insert into tanimals (fname, animal) values ('James', 'dog')
>> insert into tanimals (fname, animal) values ('Cal', 'dog')
>> insert into tanimals (fname, animal) values ('Cal', 'fish')
>> insert into tanimals (fname, animal) values ('Bret', 'fish')
>> insert into tanimals (fname, animal) values ('Kate', 'fish')
>> insert into tanimals (fname, animal) values ('Cal', 'turtle')
>>
>> I'm trying to produce
>> fname animals
>> James 2
>> Cal 3
>> Kate fish
>> Bret fish
>> if the person have more that one animals to give me the number of
>> animals, but if they own just one to return the animal that they own.
>> Thanks
>|||It is odd, but when it works, it works a charm. :-)
RLF
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OEGliNyaHHA.4008@.TK2MSFTNGP05.phx.gbl...
>> Else MAX(animal) -- Use aggregate to satisfy Group By
> LOL... That is much more clever than my approach. :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:eivaP9xaHHA.4888@.TK2MSFTNGP06.phx.gbl...
>> Select
>> fname
>> ,Case
>> When Count(animal) >1 Then Convert(nvarchar(64), Count(animal))
>> Else MAX(animal) -- Use aggregate to satisfy Group By
>> End as Animals
>> from tanimals
>> Group By fname -- Not including animal
>> order by Animals
>> RLF
>> <vncntj@.hotmail.com> wrote in message
>> news:1174415339.746907.287300@.o5g2000hsb.googlegroups.com...
>> this is my query
>> Select
>> fname
>> ,Case
>> When Count(animal) >1 Then Convert(nvarchar(64), Count(animal))
>> Else animal
>> End
>> from tanimals
>> Group By fname, animal
>> create table tanimals
>> (
>> fname nvarchar(64),
>> animal nvarchar(64),
>> )
>> insert into tanimals (fname, animal) values ('James', 'cat')
>> insert into tanimals (fname, animal) values ('James', 'dog')
>> insert into tanimals (fname, animal) values ('Cal', 'dog')
>> insert into tanimals (fname, animal) values ('Cal', 'fish')
>> insert into tanimals (fname, animal) values ('Bret', 'fish')
>> insert into tanimals (fname, animal) values ('Kate', 'fish')
>> insert into tanimals (fname, animal) values ('Cal', 'turtle')
>>
>> I'm trying to produce
>> fname animals
>> James 2
>> Cal 3
>> Kate fish
>> Bret fish
>> if the person have more that one animals to give me the number of
>> animals, but if they own just one to return the animal that they own.
>> Thanks
>>
>|||Thanks to everyone. It all works.
You guys rock!!
On Mar 20, 4:38 pm, "Russell Fields" <russellfie...@.nomail.com> wrote:
> It is odd, but when it works, it works a charm. :-)
> RLF
> "Tibor Karaszi" <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote in
> messagenews:OEGliNyaHHA.4008@.TK2MSFTNGP05.phx.gbl...
>
> >> Else MAX(animal) -- Use aggregate to satisfy Group By
> > LOL... That is much more clever than my approach. :-)
> > --
> > Tibor Karaszi, SQL Server MVP
> >http://www.karaszi.com/sqlserver/default.asp
> >http://sqlblog.com/blogs/tibor_karaszi
> > "Russell Fields" <russellfie...@.nomail.com> wrote in message
> >news:eivaP9xaHHA.4888@.TK2MSFTNGP06.phx.gbl...
> >> Select
> >> fname
> >> ,Case
> >> When Count(animal) >1 Then Convert(nvarchar(64), Count(animal))
> >> Else MAX(animal) -- Use aggregate to satisfy Group By
> >> End as Animals
> >> from tanimals
> >> Group By fname -- Not including animal
> >> order by Animals
> >> RLF
> >> <vnc...@.hotmail.com> wrote in message
> >>news:1174415339.746907.287300@.o5g2000hsb.googlegroups.com...
> >> this is my query
> >> Select
> >> fname
> >> ,Case
> >> When Count(animal) >1 Then Convert(nvarchar(64), Count(animal))
> >> Else animal
> >> End
> >> from tanimals
> >> Group By fname, animal
> >> create table tanimals
> >> (
> >> fname nvarchar(64),
> >> animal nvarchar(64),
> >> )
> >> insert into tanimals (fname, animal) values ('James', 'cat')
> >> insert into tanimals (fname, animal) values ('James', 'dog')
> >> insert into tanimals (fname, animal) values ('Cal', 'dog')
> >> insert into tanimals (fname, animal) values ('Cal', 'fish')
> >> insert into tanimals (fname, animal) values ('Bret', 'fish')
> >> insert into tanimals (fname, animal) values ('Kate', 'fish')
> >> insert into tanimals (fname, animal) values ('Cal', 'turtle')
> >> I'm trying to produce
> >> fname animals
> >> James 2
> >> Cal 3
> >> Kate fish
> >> Bret fish
> >> if the person have more that one animals to give me the number of
> >> animals, but if they own just one to return the animal that they own.
> >> Thanks- Hide quoted text -
> - Show quoted text -

Help with MS Access Error Trap

Please excuse if you feel this is posted in wrong group (I have posted in
Access groups as well)... I do know there is at least some some cross-over
base here that may help...
I am trying to establish links from Ms Access to tables in a SQL server
database. There is an Access table (tblLinkMaster) that contain all the
required linkage information. If
the database is attached and the network connection is good, the code below
works fine and all is
well with the world.
However, when I detach the database and test to see what the user would see,
I get... Connection failed: SQLSTATE:'08004' SQL Server Error: 4060 Server
rejected the
connection; Access to selected databse has been denied. If I then click OK
and cancel, the program errors
out in a messy manner.
(Program stops HERE
' Then get new link info
db1.Execute ("qapptblLinkTable"))
If the database is not available, I would prefer that the whole procedure
stop and exit gracefully.
Any Ideas on how this can be error trapped nicely ?
Thanks !
Function GetMasterLinkData()
' Based on Data in Local table (entered via form) - tblLinkMaster /
qrytblLinkMaster
Dim db1 As DAO.Database
Dim rs1 As DAO.Recordset
Set db1 = CurrentDb()
Set rs1 = db1.OpenRecordset("qrytblLinkMaster")
Dim strLinkName As String
Dim strDBName As String
Dim strTableName As String
Dim strDSNname As String
Dim strServerName As String
rs1.MoveFirst
If IsNull(rs1!LinkName) Or rs1!LinkName = "" Or _
IsNull(rs1!DatabaseName) Or rs1!DatabaseName = "" Or _
IsNull(rs1!TableName) Or rs1!TableName = "" Or _
IsNull(rs1!ServerName) Or rs1!ServerName = "" Then
MsgBox ("The Initial Set-up Infromation Is Incomplete - Please contact
an Administrator")
Exit Function
Else
strLinkName = rs1!LinkName
strDBName = rs1!DatabaseName
strTableName = rs1!TableName
strDSNname = ""
strServerName = rs1!ServerName
Call LinkTableDAO(strLinkName, strDBName, strTableName, strDSNname,
strServerName)
End If
' Now remove previous link info
db1.Execute ("DELETE from tblLinkTable")
' Then get new link info
db1.Execute ("qapptblLinkTable")
rs1.Close
Set rs1 = Nothing
db1.Close
Set db1 = Nothing
End Function
Public Function LinkTableDAO(strLinkName As String, strDBName As String,
strTableName As String,
strDSNname As String, strServerName As String)
Dim db As DAO.Database
Dim tdf As DAO.TableDef
On Error Resume Next
Set db = CurrentDb
' if link pre-exists, then delete it
Set tdf = db.TableDefs(strLinkName)
If Err.Number = 0 Then
' Found an existing tabledef
db.TableDefs.Delete strLinkName
db.TableDefs.Refresh
Else
' No existing tabledef
' Ignore error and reset
Err.Clear
End If
' Create a new TableDef object
Set tdf = db.CreateTableDef(strLinkName)
' set connect and source table table name prperties to establish link
tdf.Connect = "ODBC;Driver={SQL Server};Server=" & strServerName &
";Database=" & strDBName &
";Trusted_Connection=Yes"
tdf.SourceTableName = strTableName
' Append to the database's TableDefs collection
' IF SQL SERVER DB NOT ATTACHED - THEN ERROR OCCURS HERE
db.TableDefs.Append tdf
db.Close
End FunctionYou need to implement "On Error Goto..." error handling in your code.
See the VBA help files for more information, or post on the
Access.programming newsgroups.
--Mary
On Mon, 23 Aug 2004 10:04:54 -0400, "rob" <rwc1960@.bellsouth.net>
wrote:

>Please excuse if you feel this is posted in wrong group (I have posted in
>Access groups as well)... I do know there is at least some some cross-over
>base here that may help...
>I am trying to establish links from Ms Access to tables in a SQL server
>database. There is an Access table (tblLinkMaster) that contain all the
>required linkage information. If
>the database is attached and the network connection is good, the code below
>works fine and all is
>well with the world.
>However, when I detach the database and test to see what the user would see
,
>I get... Connection failed: SQLSTATE:'08004' SQL Server Error: 4060 Server
>rejected the
>connection; Access to selected databse has been denied. If I then click O
K
>and cancel, the program errors
>out in a messy manner.
>(Program stops HERE
>' Then get new link info
> db1.Execute ("qapptblLinkTable"))
>If the database is not available, I would prefer that the whole procedure
>stop and exit gracefully.
>Any Ideas on how this can be error trapped nicely ?
>Thanks !
>
>Function GetMasterLinkData()
>' Based on Data in Local table (entered via form) - tblLinkMaster /
>qrytblLinkMaster
> Dim db1 As DAO.Database
> Dim rs1 As DAO.Recordset
> Set db1 = CurrentDb()
> Set rs1 = db1.OpenRecordset("qrytblLinkMaster")
> Dim strLinkName As String
> Dim strDBName As String
> Dim strTableName As String
> Dim strDSNname As String
> Dim strServerName As String
> rs1.MoveFirst
> If IsNull(rs1!LinkName) Or rs1!LinkName = "" Or _
> IsNull(rs1!DatabaseName) Or rs1!DatabaseName = "" Or _
> IsNull(rs1!TableName) Or rs1!TableName = "" Or _
> IsNull(rs1!ServerName) Or rs1!ServerName = "" Then
> MsgBox ("The Initial Set-up Infromation Is Incomplete - Please contact
>an Administrator")
> Exit Function
> Else
> strLinkName = rs1!LinkName
> strDBName = rs1!DatabaseName
> strTableName = rs1!TableName
> strDSNname = ""
> strServerName = rs1!ServerName
> Call LinkTableDAO(strLinkName, strDBName, strTableName, strDSNname,
>strServerName)
> End If
> ' Now remove previous link info
> db1.Execute ("DELETE from tblLinkTable")
> ' Then get new link info
> db1.Execute ("qapptblLinkTable")
> rs1.Close
> Set rs1 = Nothing
> db1.Close
> Set db1 = Nothing
>End Function
>
>Public Function LinkTableDAO(strLinkName As String, strDBName As String,
>strTableName As String,
>strDSNname As String, strServerName As String)
> Dim db As DAO.Database
> Dim tdf As DAO.TableDef
> On Error Resume Next
> Set db = CurrentDb
> ' if link pre-exists, then delete it
> Set tdf = db.TableDefs(strLinkName)
> If Err.Number = 0 Then
> ' Found an existing tabledef
> db.TableDefs.Delete strLinkName
> db.TableDefs.Refresh
> Else
> ' No existing tabledef
> ' Ignore error and reset
> Err.Clear
> End If
> ' Create a new TableDef object
> Set tdf = db.CreateTableDef(strLinkName)
> ' set connect and source table table name prperties to establish link
> tdf.Connect = "ODBC;Driver={SQL Server};Server=" & strServerName &
>";Database=" & strDBName &
>";Trusted_Connection=Yes"
> tdf.SourceTableName = strTableName
> ' Append to the database's TableDefs collection
> ' IF SQL SERVER DB NOT ATTACHED - THEN ERROR OCCURS HERE
> db.TableDefs.Append tdf
>db.Close
>End Function
>
>
>|||I understand I have misapplied pieces of your code (page 189) in some
manner... and must still be doing that with the error trap I attempted to
add...
When I tried the code below within "LinkTableDAO", even when the database
is attached I get an error resulting in a Quit... if I take out the error
trap and the database is attached, it all works just fine.
In addition, I did post to 2 Access newsgroups with no response, then after
looking into BOL regarding SQLSTATE errors and ODBC connections, I did feel
the topic was at least somewhat relevant to 2 sql newsgroups (programming
with SQL and ODBC), my intention is certainly not to spam these fine
newsgroups.
' Append to the database's TableDefs collection
'IF SQL SERVER DB NOT ATTACHED - THEN ERROR OCCURS HERE
On Error GoTo ErrHandle
db.TableDefs.Append tdf
db.Close
ErrHandle:
Quit
Best Regards
"Mary Chipman" <mchip@.online.microsoft.com> wrote in message
news:ua3ki09utajdj3km4fhm62np3ijrek91jh@.
4ax.com...
> You need to implement "On Error Goto..." error handling in your code.
> See the VBA help files for more information, or post on the
> Access.programming newsgroups.
> --Mary
> On Mon, 23 Aug 2004 10:04:54 -0400, "rob" <rwc1960@.bellsouth.net>
> wrote:
>
cross-over[vbcol=seagreen]
below[vbcol=seagreen]
see,[vbcol=seagreen]
Server[vbcol=seagreen]
OK[vbcol=seagreen]
contact[vbcol=seagreen]
>

Help with MS Access Error Trap

Please excuse if you feel this is posted in wrong group (I have posted in
Access groups as well)... I do know there is at least some some cross-over
base here that may help...
I am trying to establish links from Ms Access to tables in a SQL server
database. There is an Access table (tblLinkMaster) that contain all the
required linkage information. If
the database is attached and the network connection is good, the code below
works fine and all is
well with the world.
However, when I detach the database and test to see what the user would see,
I get... Connection failed: SQLSTATE:'08004' SQL Server Error: 4060 Server
rejected the
connection; Access to selected databse has been denied. If I then click OK
and cancel, the program errors
out in a messy manner.
(Program stops HERE
' Then get new link info
db1.Execute ("qapptblLinkTable"))
If the database is not available, I would prefer that the whole procedure
stop and exit gracefully.
Any Ideas on how this can be error trapped nicely ?
Thanks !
Function GetMasterLinkData()
' Based on Data in Local table (entered via form) - tblLinkMaster /
qrytblLinkMaster
Dim db1 As DAO.Database
Dim rs1 As DAO.Recordset
Set db1 = CurrentDb()
Set rs1 = db1.OpenRecordset("qrytblLinkMaster")
Dim strLinkName As String
Dim strDBName As String
Dim strTableName As String
Dim strDSNname As String
Dim strServerName As String
rs1.MoveFirst
If IsNull(rs1!LinkName) Or rs1!LinkName = "" Or _
IsNull(rs1!DatabaseName) Or rs1!DatabaseName = "" Or _
IsNull(rs1!TableName) Or rs1!TableName = "" Or _
IsNull(rs1!ServerName) Or rs1!ServerName = "" Then
MsgBox ("The Initial Set-up Infromation Is Incomplete - Please contact
an Administrator")
Exit Function
Else
strLinkName = rs1!LinkName
strDBName = rs1!DatabaseName
strTableName = rs1!TableName
strDSNname = ""
strServerName = rs1!ServerName
Call LinkTableDAO(strLinkName, strDBName, strTableName, strDSNname,
strServerName)
End If
' Now remove previous link info
db1.Execute ("DELETE from tblLinkTable")
' Then get new link info
db1.Execute ("qapptblLinkTable")
rs1.Close
Set rs1 = Nothing
db1.Close
Set db1 = Nothing
End Function
Public Function LinkTableDAO(strLinkName As String, strDBName As String,
strTableName As String,
strDSNname As String, strServerName As String)
Dim db As DAO.Database
Dim tdf As DAO.TableDef
On Error Resume Next
Set db = CurrentDb
' if link pre-exists, then delete it
Set tdf = db.TableDefs(strLinkName)
If Err.Number = 0 Then
' Found an existing tabledef
db.TableDefs.Delete strLinkName
db.TableDefs.Refresh
Else
' No existing tabledef
' Ignore error and reset
Err.Clear
End If
' Create a new TableDef object
Set tdf = db.CreateTableDef(strLinkName)
' set connect and source table table name prperties to establish link
tdf.Connect = "ODBC;Driver={SQL Server};Server=" & strServerName &
";Database=" & strDBName &
";Trusted_Connection=Yes"
tdf.SourceTableName = strTableName
' Append to the database's TableDefs collection
' IF SQL SERVER DB NOT ATTACHED - THEN ERROR OCCURS HERE
db.TableDefs.Append tdf
db.Close
End Function
You need to implement "On Error Goto..." error handling in your code.
See the VBA help files for more information, or post on the
Access.programming newsgroups.
--Mary
On Mon, 23 Aug 2004 10:04:54 -0400, "rob" <rwc1960@.bellsouth.net>
wrote:

>Please excuse if you feel this is posted in wrong group (I have posted in
>Access groups as well)... I do know there is at least some some cross-over
>base here that may help...
>I am trying to establish links from Ms Access to tables in a SQL server
>database. There is an Access table (tblLinkMaster) that contain all the
>required linkage information. If
>the database is attached and the network connection is good, the code below
>works fine and all is
>well with the world.
>However, when I detach the database and test to see what the user would see,
>I get... Connection failed: SQLSTATE:'08004' SQL Server Error: 4060 Server
>rejected the
>connection; Access to selected databse has been denied. If I then click OK
>and cancel, the program errors
>out in a messy manner.
>(Program stops HERE
>' Then get new link info
> db1.Execute ("qapptblLinkTable"))
>If the database is not available, I would prefer that the whole procedure
>stop and exit gracefully.
>Any Ideas on how this can be error trapped nicely ?
>Thanks !
>
>Function GetMasterLinkData()
>' Based on Data in Local table (entered via form) - tblLinkMaster /
>qrytblLinkMaster
> Dim db1 As DAO.Database
> Dim rs1 As DAO.Recordset
> Set db1 = CurrentDb()
> Set rs1 = db1.OpenRecordset("qrytblLinkMaster")
> Dim strLinkName As String
> Dim strDBName As String
> Dim strTableName As String
> Dim strDSNname As String
> Dim strServerName As String
> rs1.MoveFirst
> If IsNull(rs1!LinkName) Or rs1!LinkName = "" Or _
> IsNull(rs1!DatabaseName) Or rs1!DatabaseName = "" Or _
> IsNull(rs1!TableName) Or rs1!TableName = "" Or _
> IsNull(rs1!ServerName) Or rs1!ServerName = "" Then
> MsgBox ("The Initial Set-up Infromation Is Incomplete - Please contact
>an Administrator")
> Exit Function
> Else
> strLinkName = rs1!LinkName
> strDBName = rs1!DatabaseName
> strTableName = rs1!TableName
> strDSNname = ""
> strServerName = rs1!ServerName
> Call LinkTableDAO(strLinkName, strDBName, strTableName, strDSNname,
>strServerName)
> End If
> ' Now remove previous link info
> db1.Execute ("DELETE from tblLinkTable")
> ' Then get new link info
> db1.Execute ("qapptblLinkTable")
> rs1.Close
> Set rs1 = Nothing
> db1.Close
> Set db1 = Nothing
>End Function
>
>Public Function LinkTableDAO(strLinkName As String, strDBName As String,
>strTableName As String,
>strDSNname As String, strServerName As String)
> Dim db As DAO.Database
> Dim tdf As DAO.TableDef
> On Error Resume Next
> Set db = CurrentDb
> ' if link pre-exists, then delete it
> Set tdf = db.TableDefs(strLinkName)
> If Err.Number = 0 Then
> ' Found an existing tabledef
> db.TableDefs.Delete strLinkName
> db.TableDefs.Refresh
> Else
> ' No existing tabledef
> ' Ignore error and reset
> Err.Clear
> End If
> ' Create a new TableDef object
> Set tdf = db.CreateTableDef(strLinkName)
> ' set connect and source table table name prperties to establish link
> tdf.Connect = "ODBC;Driver={SQL Server};Server=" & strServerName &
>";Database=" & strDBName &
>";Trusted_Connection=Yes"
> tdf.SourceTableName = strTableName
> ' Append to the database's TableDefs collection
> ' IF SQL SERVER DB NOT ATTACHED - THEN ERROR OCCURS HERE
> db.TableDefs.Append tdf
>db.Close
>End Function
>
>
>
|||I understand I have misapplied pieces of your code (page 189) in some
manner... and must still be doing that with the error trap I attempted to
add...
When I tried the code below within "LinkTableDAO", even when the database
is attached I get an error resulting in a Quit... if I take out the error
trap and the database is attached, it all works just fine.
In addition, I did post to 2 Access newsgroups with no response, then after
looking into BOL regarding SQLSTATE errors and ODBC connections, I did feel
the topic was at least somewhat relevant to 2 sql newsgroups (programming
with SQL and ODBC), my intention is certainly not to spam these fine
newsgroups.
' Append to the database's TableDefs collection
'IF SQL SERVER DB NOT ATTACHED - THEN ERROR OCCURS HERE
On Error GoTo ErrHandle
db.TableDefs.Append tdf
db.Close
ErrHandle:
Quit
Best Regards
"Mary Chipman" <mchip@.online.microsoft.com> wrote in message
news:ua3ki09utajdj3km4fhm62np3ijrek91jh@.4ax.com... [vbcol=seagreen]
> You need to implement "On Error Goto..." error handling in your code.
> See the VBA help files for more information, or post on the
> Access.programming newsgroups.
> --Mary
> On Mon, 23 Aug 2004 10:04:54 -0400, "rob" <rwc1960@.bellsouth.net>
> wrote:
cross-over[vbcol=seagreen]
below[vbcol=seagreen]
see,[vbcol=seagreen]
Server[vbcol=seagreen]
OK[vbcol=seagreen]
contact
>

Help with MAX()

Hi,
Need a query help.
Select Serial_No, MAX(Log_Time), * from logs
group by Serial_No
Gives me an error. But I want to extract the records Where Log_Time was the maximum for the corresponding Serial_No.
How do I do it?Hi,
Need a query help.

Select Serial_No, MAX(Log_Time), * from logs
group by Serial_No

Gives me an error. But I want to extract the records Where Log_Time was the maximum for the corresponding Serial_No.

How do I do it?

select * from Logs,
(Select Serial_No, MAX(Log_Time) from logs
group by Serial_No) LogMaxTime where Logs.Serial_No = LogMaxTime.Serial_No

I think that should be it ...|||Sorry. That doesn't seem to give me the result I want.

These are my data.

insert into logs (serial_no, logid, log_time, event_reason)
values ('SNI', '82738278372873','2005-10-10','Approved');

insert into logs (serial_no, logid, log_time, event_reason)
values ('SNI', '82738278372874','2005-10-11','Approved');

insert into logs (serial_no, logid, log_time, event_reason)
values ('SNI', '82738278372872','2005-10-12','Approved');

insert into logs (serial_no, logid, log_time, event_reason)
values ('SN2', '82738278372875','2005-10-13','Approved');

insert into logs (serial_no, logid, log_time, event_reason)
values ('SN2', '82738278372876','2005-10-14','Approved');

I just want the latest records for each Serial_No. In this case, I would want
the third and the fifth record.|||select * from Logs,
(Select Serial_No, MAX(Log_Time) from logs
group by Serial_No) LogMaxTime where Logs.Serial_No = LogMaxTime.Serial_No

SELECT *
FROM Logs INNER JOIN
(SELECT Serial_No, MAX(Log_Time) AS MaxOfLogs
FROM logs
GROUP BY Serial_No) LogMaxTime ON
Logs.Serial_No = LogMaxTime.Serial_No
AND Logs.Log_Time = LogMaxTime.MaxOfLogs :)|||It WORKS!!!
Thanks a lot!! :)|||Hi,
Need a query help.

Select Serial_No, MAX(Log_Time), * from logs
group by Serial_No

Gives me an error. But I want to extract the records Where Log_Time was the maximum for the corresponding Serial_No.

How do I do it?

Select Serial_No, MAX(Log_Time), * from logs
group by Serial_No having Log_Time = MAX(Log_Time)

or

Select Serial_No, Log_Time , * from logs
group by Serial_No having Log_Time = MAX(Log_Time)

I think second is the best to use. Because it is Optimised.

Regards,
Subramanyam.|||the second is not the best to use, because it is invalid syntax

:)|||...but other than that minor drawback, it is way ultra-cool.