|
More site info...
|
|
Forum profile page for ANSI SQL on http://www.dbforums.com.
This report page is the aggregated overview from a single forum: ANSI SQL, located on the Message Board at http://www.dbforums.com.
This forum profile page summarizes the general forum statistics such as: Users Activity, Forum Activity, and Top Authors, which are reported in either a table or graph below for a given reporting time period.
Additional forum profile information for "ANSI SQL" on the Message Board at http://www.dbforums.com is also shown in the following ways:
1) Latest Active Threads
2) Hot Threads for Last Week
Warning: These statistics are generated using 'best efforts' and can experience delays and reporting errors at times. Please note that such statistics do not constitute a forum's popularity and/or exact posting volumes at any given reporting period.
|
|
|
|
|
Posting activity on ANSI SQL:
|
|
Week
|
Month
|
3 Months
|
|
Threads:
|
1
|
18
|
27
|
|
Post:
|
1
|
54
|
97
|
|
|
ANSI SQL Posting activity graph:
|
Top authors during last week:
user's latest post:
query to display gaps in dates
Published (2009-11-11 18:12:00)
Quote: Originally Posted by gvee Your best option is to have a calendar table! That would indeed also be my preferred path. Some database systems support recursive SQL to generate such a table "on the fly". Most others will support it in the near future. With recursive SQL, the query becomes: Code: WITH calendar (date_field) AS RECURSIVE (SELECT CAST('2009-01-01' AS datetime) AS date_field UNION ALL SELECT...
|
|
|
|
Latest active threads on ANSI SQL::
Started 1 week, 4 days ago (2009-11-03 15:21:00)
by scooby_at_work
It's always a little tricky to ask a SQL database about stuff that doesn't exist. It's also tricky to ask a SQL database about the difference between rows.
You'll need to find all the rows in datePool such that date + 1 doesn't exist, and you need the extent of the gap:
Code:
select distinct o.dateCol + 1 as start_dt,
(select min(i.dateCol) from yourTable i where i.dateCol...
Started 2 weeks, 2 days ago (2009-10-29 10:37:00)
by BettingSherlock
Hang on, found a reference that would indicate this syntax which also works with MySQL but not entirely sure its ANSI SQL :
INSERT INTO table (fieldA,fieldB,fieldC) VALUES("text", NULL , 12345);
Would appreciate a confirmation form anyone who knows ?
Started 2 weeks, 3 days ago (2009-10-28 14:29:00)
by r937
Code:
SELECT t.NAME
, t.ANNUAL_RT
, t.Acct_CD
, t.DIST_PCT
, t.FUNDING_BEG_DT
, t.JOB_EFFSEQ
, t.ACTION_DT
FROM ( SELECT Acct_CD
, MAX(ACTION_DT) AS max_date
FROM daTable
GROUP
BY Acct_CD ) AS m
INNER
JOIN daTable AS t
ON t.Acct_CD = m.Acct_CD
AND t.ACTION_DT = m.max_date
ORDER...
Started 2 weeks, 5 days ago (2009-10-27 07:28:00)
by r937
Code:
SELECT cnt
, COUNT(*) AS count_of_cnt
FROM ( SELECT COUNT(*) AS cnt
, date
FROM reports
GROUP
BY date ) AS d
GROUP
BY cnt
Started 3 weeks, 3 days ago (2009-10-22 02:54:00)
by antigenius
oh i just found out how to do it..
select maj_code, count(*)
from student
group by maj_code
having count(*) =(select max(count(*))
from student
group by maj_code)
but sorry I didn't mention the whole question
that is display the name of the major that has the least number of advisors but the largest number of students.
and now i am having
MAJ_CODE ...
Started 3 weeks, 3 days ago (2009-10-21 11:58:00)
by gvee
Two options
1) Move the where clause to the JOIN predecate
Code:
SELECT kurs.kursnamn
, Count(kurstillf.kid) As number
FROM kurs
LEFT
JOIN kurstillf
ON kurs.kid = kurstillf.kid
AND kurstillf.year = 2008
GROUP
BY kurs.kursnamn
2) Move the WHERE clause in to a derived table
Code:
SELECT kurs.kursnamn
, Count(kurstillf.kid) As...
Started 4 weeks ago (2009-10-17 18:28:00)
by r937
Quote:
Originally Posted by andierosebud
Is the above correct ...
what happpened when you tested it?
Quote:
Originally Posted by andierosebud
... or is there another way to do this using an ANSI Join?
actually, the old style of joins is also compatible with ANSI SQL --...
Started 1 month ago (2009-10-14 03:35:00)
by JarlH
I'm not sure moving the sub-selects will improve the performance. A four-way ( left outer?) join may also be utterly slow if you haven't got the appropriate indexes.
And if you have the indexes, the current query will be pretty fast.
Started 1 month ago (2009-10-14 03:02:00)
by JarlH
Why do you want 2009-10-04 10 to be returned? That day has only one total-value.
For the rest of the rows the following query returns the result you are asking for:
SELECT date, MAX(total) - MIN(total)
FROM tablename
GROUP BY date
Note that DATE is a reserved word in ANSI SQL. It's a good idea to avoid reserved words as column names. (Or do at least double quote them, i.e "...
|
|
Hot threads for last week on ANSI SQL::
Started 1 week, 4 days ago (2009-11-03 15:21:00)
by scooby_at_work
It's always a little tricky to ask a SQL database about stuff that doesn't exist. It's also tricky to ask a SQL database about the difference between rows.
You'll need to find all the rows in datePool such that date + 1 doesn't exist, and you need the extent of the gap:
Code:
select distinct o.dateCol + 1 as start_dt,
(select min(i.dateCol) from yourTable i where i.dateCol...
|
|
Size: 1,305 bytes
