 ####   ##                   #               ##   ##  ##                
######  ##                  ##               ##   ##  ##                 
##      #####  ####  ##### #### #####  ###   ##   ##  ##                 
##      ## ##  # ##  ## ##  ##  ##  ## ##    ##   ##  ##                
##  ##  ## ## #####  ## ##  ##  ###### ##    ##   ##  ##                 
######  ## ## ## ##  ## ##  ##  ## ### ##     ## ##   ##  ##                 
 #####  ## ## #####  #####  ###  ####  ##      ###    ##  ##             
                     ##                                                  
6.11.2001            ##                                                  

napiste SELECT
1. Ali Baba | datum.nar
..
SELECT CAST( (
  SELECT COUNT(*) FROM rn_student s2 
  WHERE s1.datumnarod<=s2.datumnarod)
AS CHAR)|| '. ' || s1.meno || ' ' || s1.priezvisko, CHAR(s1.datumnarod,EUR)
  FROM rn_student s1
ORDER BY s1.datumnarod DESC

alt.
SELECT CHAR( (
  SELECT COUNT(*) FROM rn_student s2 
  WHERE s1.datumnarod<=s2.datumnarod))
  || '. ' || s1.meno || ' ' || s1.priezvisko, CHAR(s1.datumnarod,EUR)
  FROM rn_student s1
ORDER BY s1.datumnarod DESC

alt.
SELECT (
  SELECT CHAR(COUNT(*)) FROM rn_student s2 
  WHERE s1.datumnarod<=s2.datumnarod)
  || '. ' || s1.meno || ' ' || s1.priezvisko, CHAR(s1.datumnarod,EUR)
  FROM rn_student s1
ORDER BY s1.datumnarod DESC

chceme
1.|Ali Baba      |Bagdad
2.|Ruzena Sipova |Kosice
(sortovane podla mesta)

SELECT
  (SELECT count(*) 
   FROM rn_student s2 
   WHERE s2.idmesto=m1.id AND
   s2.priezvisko <= s1.priezvisko 
  ),
  s1.meno || ' ' || s1.priezvisko, m1.nazov
FROM rn_student s1 
JOIN rn_mesto m1 ON s1.idmesto=m1.id
ORDER BY m1.nazov, s1.priezvisko
1 |Ali Baba |Bagdad 
1 |Ruzena Sipova |Bardejov 
1 |Jan Hrasko |Kosice 
2 |Jan Polienko |Kosice 
3 |Juraj Trulo |Kosice 
1 |Juraj Trulo |Trebisov  

select char((select count(*) 
from mm_student st2,mm_mesto ms2 
where (st2.idmesto=ms2.id) and 
  (st2.priezvisko<=st1.priezvisko) and
  (st2.idmesto=st1.idmesto) 
group by st2.idmesto))
|| '.', st1.meno || ' ' || st1.priezvisko,ms1.nazov 

klauzula WITH
najdime najvacsi datum
SELECT MAX(datumnarod) from rn_student
1983-11-07  

druhy najvacsi datum
SELECT 

WITH novatabulka(najstarsi) AS (SELECT MAX(datumnarod) FROM rn_student)
SELECT max(datumnarod) FROM rn_student, novatabulka
WHERE novatabulka.najstarsi>datumnarod
1982-02-03  

2. najvacsi datum a menom

WITH m(najstarsi) AS
  (SELECT max(datumnarod) FROM rn_student)
SELECT meno, priezvisko, datumnarod
FROM rn_student,m
WHERE datumnarod=(SELECT max(s.datumnarod) FROM rn_student s  
WHERE s.datumnarod < m.najstarsi)
Juraj |Trulo |1982-02-03  

CREATE TABLE rn_UCITEL (
  id integer not null,
  meno VARCHAR (20) not null,
  priezvisko VARCHAR (30) not null,
  PRIMARY KEY (id)
)
CREATE TABLE rn_PREDMET (
  id integer not null,
  kod VARCHAR(10) not null,
  nazov VARCHAR(30) not null,
  iducitel integer not null,
  kredit integer not null,
  PRIMARY KEY (id)
)

CREATE TABLE rn_ZAPISANE (
  idstudent integer not null,
  idpredmet integer not null,
  znamka integer,
  primary key(idstudent, idpredmet)
)

