Database

Member 10949986 wrote:

it means i would have to create another table for the unread messages,right?

Bastard Programmer from Hell
If you can't read my code, try converting it here[^]

Eddy Vluggen wrote:

Do you need to "know" on the server whether a user read one of the messages?

Mangesh6786 wrote:

Its very very urgent.

Bastard Programmer from Hell
If you can't read my code, try converting it here[^]

SELECT * FROM
(
SELECT EMpNum "EmpNum", EmergencyContact1 "Name", Relation1 "Relation", PhType1 "Phone Type", Phone1 "Phone"
FROM EMERGENCY_CONTACT

UNION

SELECT EMpNum "EmpNum", EmergencyContact2 "Name", Relation2 "Relation", PhType2 "Phone Type", Phone2 "Phone"
FROM EMERGENCY_CONTACT

) EMERGENCY_CONTACTS

ORDER BY "EmpNum", "Name", "Phone Type"

WITH cteOrderedContacts As
(
    SELECT
        EmpNum,
        EmpEmergencyContact,
        Relation,
        PhType,
        Phone,
        
        -- Generate a row number for each employee, ordered by contact name:
        ROW_NUMBER() OVER (PARTITION BY EmpNum ORDER BY EmpEmergencyContact) As RN
    FROM
        SourceData
)
SELECT
    C1.EmpNum,
    C1.EmpEmergencyContact As EmergencyContact1,
    C1.Relation As Relation1,
    C1.PhType As PhType1,
    C1.Phone As Phone1,
    C2.EmpEmergencyContact As EmergencyContact2,
    C2.Relation As Relation2,
    C2.PhType As PhType2,
    C2.Phone As Phone2 
FROM
    cteOrderedContacts As C1
    LEFT JOIN cteOrderedContacts As C2
    ON C2.EmpNum = C1.EmpNum  -- Record must be for the same employee;
    And C2.RN = C1.RN + 1     -- Get the next record in the sequence;
WHERE
    -- Only return odd-numbered rows, as the even-numbered rows
    -- will be included in the previous row:
    (C1.RN & 1) = 1
ORDER BY
    C1.EmpNum,
    C1.RN
;

create table products(product_id int, next_id int, product_name nvarchar(50));
insert into products(product_id, next_id, product_name)
Values (1, null, N'product1')
,(2, 4, N'product2')
,(3, 2, N'product3')
,(4, 1, N'product4')
,(5, 3, N'product5');

WITH LinkedList (product_id, next_id, product_name, Level)
AS
(
-- Anchor member definition
    SELECT e.product_id, e.next_id, e.product_name, 
        0 AS Level
    FROM products AS e
    WHERE e.next_id IS NULL
    UNION ALL
-- Recursive member definition
    SELECT e.product_id, e.next_id, e.product_name,
        Level + 1
    FROM products AS e
    INNER JOIN LinkedList AS d
        ON e.next_id = d.product_id
)
-- Statement that executes the CTE
SELECT product_id, next_id, product_name
FROM LinkedList
Order by Level desc;

drop Table products;

BEGIN TRANSACTION

	CREATE TABLE SomeTest(
		Field1 INTEGER,
		Field2 CHAR(1),
		Data VARCHAR(50))
		
	INSERT INTO SomeTest VALUES (1, 'C', 'Test')
	INSERT INTO SomeTest VALUES (1, 'A', 'Test')
	INSERT INTO SomeTest VALUES (7, 'C', 'Test')
	INSERT INTO SomeTest VALUES (7, 'A', 'Test')
	INSERT INTO SomeTest VALUES (4, 'C', 'Test')

    SELECT *
         , ROW_NUMBER() OVER ( PARTITION BY Field2 ORDER BY Field1 ) 
      FROM SomeTest
    
ROLLBACK 

Bastard Programmer from Hell
If you can't read my code, try converting it here[^]

Eddy Vluggen wrote:

some idiot forgot to added an identity-field