Article:		SQL Subqueries
Subject:		Count the number of record first existing in table at a date
Date:		2006-10-16 20:50:17
From:		Devils
Hi, I have proble to find the number of single record in a table. e.g. field A | field B 111 | 1/10/2005 111 | 2/10/2005 222 | 2/10/2005 333 | 2/10/2005 222 | 3/10/2005 I want to know how many record first exist at 2/10/2005. The result should be 2. i.e. 111 first exist is 1/10/2005 -> not count 222 first exist is 2/10/2005 -> count 333 first exist is 2/10/2005 -> count Can you help how to do this? Regards, Devils

Showing messages 1 through 6 of 6.

• Count the number of record first existing in table at a date
  2006-10-29 01:25:20  ramu82 [Reply | View]
  
  
  select distinct(field A) from date

• Count the number of record first existing in table at a date
  2006-12-13 05:39:38  jmtsnprn [Reply | View]
  
  
  the query could be:
  select count(fielda) from table where fieldb = '2005-02-10'
  

• Count the number of record first existing in table at a date
  2007-01-22 08:13:14  nikberry [Reply | View]
  
  
  I haven't tried this, but something like:
  
  select fielda,fieldb from table
  where fieldb = '2/10/2005' and
  fielda not in( select fielda from table
  where fieldb < '2/10/2005')
  
• Count the number of record first existing in table at a date
  2007-01-22 08:15:02  nikberry [Reply | View]
  
  
  Obviously you'd need count() too :).
  

• Count the number of record first existing in table at a date
  2007-12-17 02:49:44  Aryanrules [Reply | View]
  
  
  Hi Devil,
  its simple man
  I think by this time you might hv got the answer if not use this query
  
  SELECT * FROM table_name where fieldB ='2/10/2005' LIMIT 2
  
  regards,
  Aryan
  
  
  
  
  
  
• Count the number of record first existing in table at a date
  2009-04-28 04:23:25  SCJPSandy [Reply | View]
  
  
  Hi,
  i tried the same one but shows an error as Incorrect syntax near 'LIMIT'.