Women in Technology

Hear us Roar



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

Full Threads Newest First

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 [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 [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 [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 [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 [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 [View]

      Hi,
      i tried the same one but shows an error as Incorrect syntax near 'LIMIT'.