SQL for Smarties | SQL Programming Style | Trees and Hierarchies in SQL | SQL Puzzles and Answers | Data and Databases


Thursday, October 04, 2007

How to retrieve all records from 30th record to 50th record of a table?

SQL Apprentice Question
I have a table named myTable. It is sorted by some column and there is
no primery key or unique column.I want to retrieve all records from
30th record to 50th record. How can I do this by a sql statement?



Celko Answers
>> I have a table named myTable. It is sorted by some column and there is no primary key or unique column. <<


BY DEFINITION:
1) Tables have at least one key
2) Tables have no ordering


>> I want to retrieve all records from 30th record to 50th record. <<


1) Rows are not anything like records
2) Tables have no ordering

Please read a book --ANY book -- are RDBMS. You have gotten every
basic concept wrong.




Original Source

No idea where to start with Query

SQL Apprentice Question
I need some help with a query using SQL 2005 and I really don't know
where to start.
I have 3 columns: Date(dateTime), Name(varchar), Value(decimal)
I need to retireive 3 values, one query or all, or one query for
each. It makes no difference to me.

1)For each Name I need to get the difference of current day's value
and previous day's value
(TodayValue- PreviousDayValue)


2)For each Name I need to get the sum of difference of yesterday's
value and today's value for the current month
So something like (Day1Value - Day2Value) + (Day2Value - Day3Value)
+...+ (Day29Value - Day30Value)


3)Same as #2 but just for the current year.


Any help would be greatly appreciated.



Celko Answers
The best place to start is with DDL, so that people do not have to
guess what the keys, constraints, Declarative Referential Integrity,
data types, etc. in your schema are. If you know how, follow ISO-11179
data element naming conventions and formatting rules. Sample data is
also a good idea, along with clear specifications. It is very hard to
debug code when you do not let us see it. What you did post was a pile
of vague names and/or reserved words. Let's make it real and sensible:

CREATE TABLE DogSchedule
(walk_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
dog_name CHAR(15) NOT NULL,
walk_kilometers DECIMAL (5,2) NOT NULL,
PRIMARY KEY (walk_date, dog_name));


1)For each dog_name I need to get the difference of current day's
value and previous day's value <<


You did not tell us if you are sure that all days are represented in
the table? Only one walk per day? What constraint enforces that
business rule? My point is that SQL is an integrated whole -- you
cannot separate DDL and DML; they must work together!


Next, you are going to be using the delta from day to day, so let's
put that in a VIEW.


CREATE VIEW DeltaDogWalks (walk_date, dog_name, delta_kilometers)
AS
SELECT D2.walk_date, D2.dog_name,
(D2.walk_kilometers - D1.walk_kilometers)
FROM DogSchedule AS D1, DogSchedule AS D2
WHERE D1.dog_name = D2.dog_name
AND D2.walk_date = DATEADD(DD, 1, D1.walk_date);


To sum the deltas, set up a report periods table that you can adjust:


CREATE TABLE ReportPeriods
(period_name CHAR(15) NOT NULL PRIMARY KEY,
start_date DATETIME NOT NULL,
end_date DATETIME NOT NULL,
CHECK(start_date < end_date));


Now use a "walk_date BETWEEN start_date AND end_date" to group on the
names of the reporting periods.


Original source