Finding last entries

Hi, I am searching for the most easy SQL solution:

Lets say i have 500000 rows of cars in one table with 30000 different car ID's.
This table contains no keys, no date values, but I need the last row of these 30000 different cars. How do I get them without adapting table structure and without using cursors ?

Thx for reply.

dajmHow's your resume' looking these days? Is it up to date?|||The last row updated? The last row inserted? Physical location might not really mean anything.

Are there any related tables that could help us out.

You might be screwed if you want to figure this out in your current setup.

Going forward you could use a trigger to populate a new table for this purpose.|||If you use TOP 1 you get a result that is as good as any.
Noone can say that it is false, because if they can, they must say why, and if they say why, then you have a rule you could use in your select statement.

That's what I should've done in your shoes, given that there was noone to ask that is ...

As Thrasymachus says, there is no way (with the given info) that the solution can be found, atleast not that I can see.|||O.K. Here's an example for everybody

Car-ID |sales price

1|5
3|3
2|6
1|7
3|4

As result I need to get
2|6
1|7
3|4

With Navision language C/Side it is very easy to catch the last entries of database by setting a filter on car-id, but how do I get this result by using SQL ?|||Example pasted from earlier post :

Car-ID |sales price

1|5
3|3
2|6
1|7
3|4

As result I need to get
2|6
1|7
3|4

Nothing tells you that these three last entries in your result is in fact the three last ones. All you know is that some means of presentation choose to show you those results in that order. Running the same question again or from another client might return other results. Therefore you still haven't presented any useful info to solve the problem, unless you wanna use my TOP solution earlier. In that case replace the TOP 1 with TOP 3 ...|||Data in a database is not guaranteed to be in any order unless specified in the table design. If this table has a clustered index (as it should), then the data is no longer in the order in which it was entered.

Period.

If you do not store the datetime the record was entered, you cannot expect to sort or filter on the datetime the record was entered.|||There exists no index, but the no. of cars is dynamic.
I cannot use a "select top n" statement as I never know "n".
Can I combine this however with a "select count (distinct car-id)" ?

dajm|||Sorry, all back. There is a clustered index on a integer field with incremental no., used as a uniqueidentifier.
Does this help somehow ?|||well yes. but you said there were no keys earlier no keys...

and then you said the keys were not dynamic

"Lets say i have 500000 rows of cars in one table with 30000 different car ID's"

just use the max() function on your newly discovered field.|||A max() on this field returns just the last row, but not every last row of my 30000 vehicles, means 30000 rows.|||See this thread for a solution to your problem:

http://www.dbforums.com/showthread.php?t=1094858&page=1&pp=15

find last data according to date...

hi..
i'm still newbie in SSRS 2005..
i've a problem about it..lets say i have a data like that :
date_rent type_rent
1/1/2006 CAR
1/1/2006 LORRY
1/2/2006 BUS
......
......
......
......
11/2/2006 LORRY
1/3/2006 CAR
3/3/2006 CAR
4/3/2006 BUS
at my report has one input date parameter..if i input the date:
30/03/2006 and i want to find the last of date_rent which refer to
CAR...meaning the result is
3/3/2006...
or if i input the date : 1/2/2006
the result of last of date_rent which refer to CAR...is 1/1/2006
...can anybody help me'
thanks in advance...Try:
SELECT type_rent, MAX(date_rent)
FROM MyTable
WHERE date_rent <= @.InputDate
GROUP BY type_rent
HTH
--
Magendo_man
Freelance SQL Reporting Services developer
Stirling, Scotland
"imapeace" wrote:
> hi..
> i'm still newbie in SSRS 2005..
> i've a problem about it..lets say i have a data like that :
> date_rent type_rent
> 1/1/2006 CAR
> 1/1/2006 LORRY
> 1/2/2006 BUS
> ......
> ......
> ......
> ......
> 11/2/2006 LORRY
> 1/3/2006 CAR
> 3/3/2006 CAR
> 4/3/2006 BUS
> at my report has one input date parameter..if i input the date:
> 30/03/2006 and i want to find the last of date_rent which refer to
> CAR...meaning the result is
> 3/3/2006...
> or if i input the date : 1/2/2006
> the result of last of date_rent which refer to CAR...is 1/1/2006
> ...can anybody help me'
> thanks in advance...
>|||Thank you for ur response..
how about if i want to put it in expression'It is because my report
has one table and many functions...every field i used an expression
according to different condition...
-tq
magendo_man (donotspam) wrote:
> Try:
> SELECT type_rent, MAX(date_rent)
> FROM MyTable
> WHERE date_rent <= @.InputDate
> GROUP BY type_rent
> HTH
> --
> Magendo_man
> Freelance SQL Reporting Services developer
> Stirling, Scotland
>
> "imapeace" wrote:
> > hi..
> > i'm still newbie in SSRS 2005..
> > i've a problem about it..lets say i have a data like that :
> >
> > date_rent type_rent
> > 1/1/2006 CAR
> > 1/1/2006 LORRY
> > 1/2/2006 BUS
> > ......
> > ......
> > ......
> > ......
> > 11/2/2006 LORRY
> > 1/3/2006 CAR
> > 3/3/2006 CAR
> > 4/3/2006 BUS
> >
> > at my report has one input date parameter..if i input the date:
> > 30/03/2006 and i want to find the last of date_rent which refer to
> > CAR...meaning the result is
> > 3/3/2006...
> > or if i input the date : 1/2/2006
> > the result of last of date_rent which refer to CAR...is 1/1/2006
> >
> > ...can anybody help me'
> >
> > thanks in advance...
> >
> >|||You could set up a table in which you define detail grouping based on
Fields!type_rent.Value, and filtering Fields!date_rent.Value <=Parameters!InputDate.value. On the detail row you could then have two
textboxes:
1) =Fields!type_rent.Value
2) =Max(Fields!date_rent.Value)
HTH
--
Magendo_man
Freelance SQL Reporting Services developer
Stirling, Scotland
"imapeace" wrote:
> Thank you for ur response..
> how about if i want to put it in expression'It is because my report
> has one table and many functions...every field i used an expression
> according to different condition...
> -tq
> magendo_man (donotspam) wrote:
> > Try:
> >
> > SELECT type_rent, MAX(date_rent)
> > FROM MyTable
> > WHERE date_rent <= @.InputDate
> > GROUP BY type_rent
> >
> > HTH
> >
> > --
> >
> > Magendo_man
> >
> > Freelance SQL Reporting Services developer
> > Stirling, Scotland
> >
> >
> > "imapeace" wrote:
> >
> > > hi..
> > > i'm still newbie in SSRS 2005..
> > > i've a problem about it..lets say i have a data like that :
> > >
> > > date_rent type_rent
> > > 1/1/2006 CAR
> > > 1/1/2006 LORRY
> > > 1/2/2006 BUS
> > > ......
> > > ......
> > > ......
> > > ......
> > > 11/2/2006 LORRY
> > > 1/3/2006 CAR
> > > 3/3/2006 CAR
> > > 4/3/2006 BUS
> > >
> > > at my report has one input date parameter..if i input the date:
> > > 30/03/2006 and i want to find the last of date_rent which refer to
> > > CAR...meaning the result is
> > > 3/3/2006...
> > > or if i input the date : 1/2/2006
> > > the result of last of date_rent which refer to CAR...is 1/1/2006
> > >
> > > ...can anybody help me'
> > >
> > > thanks in advance...
> > >
> > >
>