retrieve record from database problem

i m using sql server 2000 with asp.net with c#

i hv 4 customer records in the customer table starting from C1001 to C1004, i wanna ask is that when a new record is add to the table, the record will be placed at the bottom of the table. For example,
CustomerID

Customer IDC1001C1002C1003C1004C1005

When i add a new record which is C1005, is it the record will be placed as shown in the table?

if so, that's mean can straight away use the datarow to retrieve the largest number which is C1005, right?

Thx

In most cases the answer is yes, even the new record is C1000, it till be append to the end of the table. When you select data from the table withoutORDER BY clause, the order of returned rows wil be the same as the they had been inserted. To ensure you get the row with max id, please use TOP..ORDER BY DESC:

SELECT top 1* FROM yourTable ORDER BY CustomerID DESC

Or use the MAX function if you only want the max id:

SELECT MAX(CustomerID) FROM yourTable

retrieve only new customers with orders

I have a table with orders and customer info. I want to retrieve only customers with new orders only. These are the available fields: customer Id, Order Id, Order Date, and Order Subtotal. I need help with the "where" section of the query.

Hi Ray, this mainly depends on how you define "new" relative to the date values in your data. But assuming "new" means orders placed today, you could use something like "where datediff(dd,OrderDate,getdate()) = 0". You can review the SQL Server books online for more information about getdate(), datediff(), or the other date functions you could use here.

Thanks, Dean

retrieve first record of each customer

I have to retrieve first and last record of each customer according to the Date. Each customer has 10 - 15 records in the table and there are 3000 customers.
how can I retrieve this data.

regardsYou should do something like this:

select *
from table_name a
join (select customer_id, max(date) max_date, min(date) min_date
from table_name
group by custormer_id) b on a.customer_id = b.customer_id and
(date = max_date or date = min_date )

Good Luck.|||Unless you need field information from both records on the one line then you could use:

SELECT
customers.customerid
(SELECT Top 1 c1.DetailsID FROM customerdetails c1 ORDER BY c1.CreateDate ASC) DetailsIDFirst,
(SELECT Top 1 c1.DetailsID FROM customerdetails c1 ORDER BY c1.CreateDate DESC) DetailsIDLast
FROM customers

I'm bringing back the DetailsID - but you could recall any field using this.

Hope this helps.