1

Topic: Request. Grouping with numbering

The initial table
NAME, DATE
, 2017-11-01
Ivanov, 2017-11-01
Smiths, 2017-11-01
Sokolov, 2017-11-05
Priests, 2017-11-05
Lebedev, 2017-11-06
Goats, 2017-11-06
, 2017-11-06
Frosts, 2017-11-09
Petrov, 2017-11-09
Wolves, 2017-11-09
Nightingales, 2017-11-09
, 2017-11-15
Hares, 2017-11-15
Pavels, 2017-11-15
[spoiler CREATE TABLE]

CREATE TABLE "DOC" (
NAME TEXT,
DATE DATE
);
INSERT INTO "DOC" (NAME, DATE) VALUES ("Smirnov", "2017-11-01");
INSERT INTO "DOC" (NAME, DATE) VALUES ("Ivanov", "2017-11-01");
INSERT INTO "DOC" (NAME, DATE) VALUES ("Smiths", "2017-11-01");
INSERT INTO "DOC" (NAME, DATE) VALUES ("Sokolov", "2017-11-05");
INSERT INTO "DOC" (NAME, DATE) VALUES ("Priests", "2017-11-05");
INSERT INTO "DOC" (NAME, DATE) VALUES ("Lebedev", "2017-11-06");
INSERT INTO "DOC" (NAME, DATE) VALUES ("Goats", "2017-11-06");
INSERT INTO "DOC" (NAME, DATE) VALUES ("Novikov", "2017-11-06");
INSERT INTO "DOC" (NAME, DATE) VALUES ("Frosts", "2017-11-09");
INSERT INTO "DOC" (NAME, DATE) VALUES ("Petrov", "2017-11-09");
INSERT INTO "DOC" (NAME, DATE) VALUES ("Wolves", "2017-11-09");
INSERT INTO "DOC" (NAME, DATE) VALUES ("Nightingales", "2017-11-09");
INSERT INTO "DOC" (NAME, DATE) VALUES ("Vasilev", "2017-11-15");
INSERT INTO "DOC" (NAME, DATE) VALUES ("Hares", "2017-11-15");
INSERT INTO "DOC" (NAME, DATE) VALUES ("Pavels", "2017-11-15")

[/spoiler]
It is necessary to receive the table with grouped and enumerated records:
No /, Date, Kol-in
1, 2017-11-01, 3
2, 2017-11-05, 2
3, 2017-11-06, 3
4, 2017-11-09, 4
5, 2017-11-15, 3
Help to make request. complexity in numbering :

SELECT "?" AS "No /",
"DATE" as "Date",
COUNT (*) AS "Kol-in"
FROM "DOC" AS B GROUP BY "DATE";

2

Re: Request. Grouping with numbering

with doc_cnt as (select date, count (*) as cnt from doc group by date)
select (select count (*) +1 from doc_cnt ds where ds.date <do.date), date, cnt from doc_cnt do;

But generally, numbering of lines it is necessary to do on the client.

3

Re: Request. Grouping with numbering

White Owl;
Thanks, request work in SQLiteStudio.
But it appeared that the component used by me does not understand the instruction with.
While made through the temporary table

CREATE TEMP TABLE IF NOT EXISTS "DOC2" (DATE REAL, NUM INT, DESCR TEXT);
INSERT INTO "DOC2" SELECT "DATE", COUNT ("DATE"), GROUP_CONCAT ("NAME") FROM "DOC" GROUP BY "DATE" ORDER BY "DATE" ASC;
SELECT ROWID as "No /", "DATE" AS "Date", "NUM" AS "Kol-in", "DESCR" AS "Remark" FROM "DOC2";

4

Re: Request. Grouping with numbering

Ghost Writer wrote:

White Owl;
Thanks, request work in SQLiteStudio.
But it appeared that the component used by me does not understand the instruction with.

It as well as why??? It is syntax of the engine.

Ghost Writer wrote:

While made through the temporary table

CREATE TEMP TABLE IF NOT EXISTS "DOC2" (DATE REAL, NUM INT, DESCR TEXT);
INSERT INTO "DOC2" SELECT "DATE", COUNT ("DATE"), GROUP_CONCAT ("NAME") FROM "DOC" GROUP BY "DATE" ORDER BY "DATE" ASC;
SELECT ROWID as "No /", "DATE" AS "Date", "NUM" AS "Kol-in", "DESCR" AS "Remark" FROM "DOC2";

it is better to transfer all the same this functional on the client. And is easier, and faster, and on sorting will not depend.

5

Re: Request. Grouping with numbering

White Owl wrote:

It as well as why??? It is syntax of the engine.

it is a component for Delphi DISQLite3 with own implementation API without usage original sqlite3.dll

White Owl wrote:

it is better to transfer all the same this functional on the client.

Earlier and was. I will receive  now ready reports then not to rewrite the client, and simply to add in the list of reports a report name + sql. We look as it turns out.

White Owl wrote:

and on sorting will not depend.

anyway it is better to me to sort.

6

Re: Request. Grouping with numbering

Ghost Writer;
The temporary table is not necessary. At once it is possible to make all one request.... Including - without WITH

7

Re: Request. Grouping with numbering

Kind  - Eh;
, it is a pity that did not show as)

8

Re: Request. Grouping with numbering

Ghost Writer wrote:

Kind  - Eh;
, it is a pity that did not show as)

Well for example so.

select (select count (distinct date) +1 from doc b where b.date <a.date), date, count (*) from doc a group by date;

But you on Delfi write? In the same place high-grade MVC with ease becomes. What for to you to transfer functions View to a DBMS???

9

Re: Request. Grouping with numbering

White Owl;
Many thanks! Elegantly it turned out)>: - <

White Owl wrote:

What for to you to transfer functions View to a DBMS???

did not understand that you mean. What reports to look in a DBMS? smile))

10

Re: Request. Grouping with numbering

Ghost Writer wrote:

it is passed...
Did not understand that you mean. What reports to look in a DBMS? smile))

The standard approach to operation from a DBMS: https://en.wikipedia.org/wiki/Model-view-controller
Ideally lays down on TDataSet-TDBGrid-TDataSource.
Thus TDBGrid already has numbering of lines and to drag it from basis it is absolutely not necessary.

11

Re: Request. Grouping with numbering

I see. No, I do not use TDBGrid. At once in the necessary format (Excel, html, csv, etc.)
Also did not hear that at TDBGrid there is such feature. TDataSet can (DataSet. RecNo in OnCalcField)