RANK() Function on DATE IN SQL

I am using PopSQL for this.

Please consider the following: I am trying to rank each country with PayDate. For each PayDate, there is more than one country linked. My wish is to group each country with specific PayDate or its count and rank them accordingly.

CREATE TABLE Country
(
    CountryID INT PRIMARY KEY,
    CountryName VARCHAR(40)
);

CREATE TABLE State
(
    StateID INT PRIMARY KEY,
    StateName VARCHAR(40),
    CountryID INT,
    FOREIGN KEY(CountryID) REFERENCES Country(CountryID)
);

CREATE TABLE City
(
    CityID INT PRIMARY KEY,
    CityName VARCHAR(40),
    StateID INT,
    FOREIGN KEY(StateID) REFERENCES State(StateID)
);

CREATE TABLE Rooms 
(
    RoomID INT PRIMARY KEY,
    RoomTypeID INT,
    RoomBandID INT,
    RoomFacilityID INT,
    CityID INT,
    Floor INT,
    AddionalNotes VARCHAR(255),
    FOREIGN KEY(RoomTypeID) REFERENCES RoomType(RoomTypeID),
    FOREIGN KEY(RoomBandID) REFERENCES RoomBand(RoomBandID),
    FOREIGN KEY(RoomFacilityID) REFERENCES RoomFacility(RoomFacilityID),
    FOREIGN KEY(CityID) REFERENCES City(CityID)
);

CREATE TABLE DT_Date
(
    DateID INT NOT NULL,
    FullDate Datetime NOT NULL, 
    DateMonth INT NOT NULL, 
    Quarter INT NOT NULL,
    DateYear year NOT NULL, 
    PRIMARY KEY(DateID)
);

CREATE TABLE Customer
(
    CustomerID INT PRIMARY KEY,
    CustomerForename VARCHAR(20),
    CustomerSurname VARCHAR(20),
    CustomerDOB DATetime,
    CustomerHomePhone INT,
    CustomerMobilePhone INT,
    CustomerWorkPhone INT,
    CustomerEmail VARCHAR(40),
    CityID INT,
    FOREIGN KEY (CityID) REFERENCES City(CityID)

);

CREATE TABLE Payments
(
    PaymentID INT PRIMARY KEY, 
    PaymentComment VARCHAR(255), 
    PaymentsMethodID INT, 
    PayDate Datetime,
    RoomID INT,
    DateID INT,
    CustomerID INT,
    Price INT,
    PaymentAmount INT,
    FOREIGN KEY(RoomID) REFERENCES Rooms(RoomID),
    FOREIGN KEY(PaymentsMethodID) REFERENCES PaymentsMethod(PaymentsMethodID),
    FOREIGN KEY(DateID) REFERENCES DT_Date(DateID),
    FOREIGN KEY(CustomerID) REFERENCES Customer(CustomerID)
);

Please also have a look at my last attempt:

select 
    rank() over (PARTITION BY CountryName order by count(PayDate)),
    CountryName, PayDate, count(Paydate)
from 
    City, Payments, Rooms, Customer, State, Country, DT_Date
where
    Payments.PayDate >= "2010-00-00 00:00:00" 
    and Payments.CustomerID = Customer.CustomerID
    and State.CountryID = Country.CountryID
    and City.StateID = State.StateID
    and Customer.CityID = City.CityID
    and Payments.DateID = DT_Date.DateID
group by 
    CountryName, PayDate;

The query worked. However, results are not correct because there are two PayDates for UK and one PayDate for USA. Following is the result shown:

Rank CountryName         PayDate       count(Paydate)
1       UK        2015-12-31 00:00:00    4
1       UK        2014-06-10 00:00:00    4
1       USA       2011-11-25 00:00:00    4

Expected output:

Rank CountryName        
1       UK        

2       USA

Solution

Don't group by PayDate, only by CountryName. This means you cannot include the PayDate in the results, but you don't want that anyway.

Also remove CountryName from your partitioning. Your rank is just ordered by the PayDate count.

And to have the largest count ranked first, use a descending order.

select
  rank() over (order by count(PayDate) desc ) as "rank",
  CountryName
from City , Payments,Rooms,Customer,State,Country,DT_Date
WHERE Payments.PayDate >= '2010-00-00 00:00:00'
ANd Payments.CustomerID = Customer.CustomerID
And State.CountryID = Country.CountryID
AND City.StateID = State.StateID
And Customer.CityID = City.CityID
and Payments.DateID = DT_Date.DateID
GROUP by CountryName

Here's a simplified demo.

A few side notes.

Though it might work in your SQL implementation, 2010-00-00 00:00:00 is a dodgy datetime. Use 2010-01-01 00:00:00 or better your database's equivalent of year(Payments.PayDate) >= 2010

Don't group by CountryName, group by Country.CountryId. Grouping by name invites accidentally grouping two things together, and unique country names are not enforced in the schema (they should be).

Finally, explicit joins do make it easier to understand and maintain a query.