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 Views with NOEXPAND

Hi,
Anyone out there know how to tell whether a view uses the WITH (NOEXPAND)
hint (other than searching the syscomments table for 'NOEXPAND')?
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
Another method is to script the view create statements and then search the
scripts for the NOEXPAND keyword.
Hope this helps.
Dan Guzman
SQL Server MVP
"Daniel Jameson" <djameson@.childrensoncologygroup.org> wrote in message
news:ejRg3RwQGHA.5924@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Anyone out there know how to tell whether a view uses the WITH (NOEXPAND)
> hint (other than searching the syscomments table for 'NOEXPAND')?
> --
> Thank you,
> Daniel Jameson
> SQL Server DBA
> Children's Oncology Group
> www.childrensoncologygroup.org
>

Find Views with NOEXPAND

Hi,
Anyone out there know how to tell whether a view uses the WITH (NOEXPAND)
hint (other than searching the syscomments table for 'NOEXPAND')?
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.orgAnother method is to script the view create statements and then search the
scripts for the NOEXPAND keyword.
Hope this helps.
Dan Guzman
SQL Server MVP
"Daniel Jameson" <djameson@.childrensoncologygroup.org> wrote in message
news:ejRg3RwQGHA.5924@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Anyone out there know how to tell whether a view uses the WITH (NOEXPAND)
> hint (other than searching the syscomments table for 'NOEXPAND')?
> --
> Thank you,
> Daniel Jameson
> SQL Server DBA
> Children's Oncology Group
> www.childrensoncologygroup.org
>sql

Find Views with NOEXPAND

Hi,
Anyone out there know how to tell whether a view uses the WITH (NOEXPAND)
hint (other than searching the syscomments table for 'NOEXPAND')?
--
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.orgAnother method is to script the view create statements and then search the
scripts for the NOEXPAND keyword.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Daniel Jameson" <djameson@.childrensoncologygroup.org> wrote in message
news:ejRg3RwQGHA.5924@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Anyone out there know how to tell whether a view uses the WITH (NOEXPAND)
> hint (other than searching the syscomments table for 'NOEXPAND')?
> --
> Thank you,
> Daniel Jameson
> SQL Server DBA
> Children's Oncology Group
> www.childrensoncologygroup.org
>

find text string in database

Hello,
I'd like to find a specific text string searching in all tables within same database.
Is there a way to make only one query to all tables at the same time?
Thank you very much for your attention.
QslxNo. You would have to write a procedure that looped through all the tables and checked every column.

This question makes me suspect that there are some design issues with your database schema.

blindman|||Ya mean like:

USE Northwind
GO

CREATE TABLE myTable99 (TABLE_NAME sysname, COLUMN_NAME sysname, Occurs int)
GO

SET NOCOUNT ON

DECLARE @.SQL varchar(8000), @.TABLE_NAME sysname, @.COLUMN_NAME sysname, @.Sargable varchar(80), @.Count int

SELECT @.Sargable = 'Beer'

DECLARE insaneCursor CURSOR FOR
SELECT c.TABLE_NAME, c.COLUMN_NAME
FROM INFORMATION_SCHEMA.Columns c INNER JOIN INFORMATION_SCHEMA.Tables t
ON t.TABLE_SCHEMA = c.TABLE_SCHEMA AND t.TABLE_NAME = c.TABLE_NAME
WHERE c.DATA_TYPE IN ('char','nchar','varchar','nvarchar','text','ntext ')
AND t.TABLE_TYPE = 'BASE TABLE'

OPEN insaneCursor

FETCH NEXT FROM insaneCursor INTO @.TABLE_NAME, @.COLUMN_NAME

WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT @.SQL = 'INSERT INTO myTable99 (TABLE_NAME, COLUMN_NAME, Occurs) SELECT '
+ '''' + @.TABLE_NAME + '''' + ','
+ '''' + @.COLUMN_NAME + '''' + ','
+ 'COUNT(*) FROM [' + @.TABLE_NAME
+ '] WHERE [' + @.COLUMN_NAME + '] Like '
+ ''''+ '%' + @.Sargable + '%' + ''''
--SELECT @.SQL
EXEC(@.SQL)
IF @.@.ERROR <> 0
BEGIN
SELECT @.SQL
SELECT * FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME = @.TABLE_NAME
GOTO Error
END
FETCH NEXT FROM insaneCursor INTO @.TABLE_NAME, @.COLUMN_NAME
END

SELECT * FROM myTable99 WHERE Occurs <> 0

Error:
CLOSE insaneCursor
DEALLOCATE insaneCursor

GO

DROP TABLE myTable99
GO

SET NOCOUNT OFF|||Hi Brett,
Thanks a lot for you help.
It works great!

Cheers,|||brett, don't you have any hobbies? :p|||Yeah...SQL

That was a cut and paste from my toolbox...

You kidding...I'm writting a sql server version of the window explorer find function...using xp_cmdshell, because it's tooooooooooooo painful to deal with server ops...|||I can't tell when you're kidding and when you're not!|||I'd say I'm an Enigma...but that's taken already...8-)

And I finally got server ops to give me clearence, so I won't have to build the explorer

Are Margarittas a hobby?