Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 928DB474E53 for ; Wed, 27 Nov 2002 05:06:21 -0500 (EST) Received: from smtp.ualg.pt (smtp.ualg.pt [193.136.224.8]) by postgresql.org (Postfix) with ESMTP id A3D1A475E88 for ; Wed, 27 Nov 2002 05:05:30 -0500 (EST) Received: from localhost (smtp.ualg.pt [127.0.0.1]) by smtp.ualg.pt (Postfix) with ESMTP id D7DBD4BCFA; Wed, 27 Nov 2002 10:05:30 +0000 (WET) Received: from ualg.pt (corisco.uceh.ualg.pt [10.10.20.126]) by smtp.ualg.pt (Postfix) with ESMTP id 892874BCFC; Wed, 27 Nov 2002 10:05:28 +0000 (WET) Message-ID: <3DE498E4.2050002@ualg.pt> Date: Wed, 27 Nov 2002 10:05:24 +0000 From: Luis Sousa User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.0.0) Gecko/20020623 Debian/1.0.0-0.woody.1 MIME-Version: 1.0 To: "Arcadius A." Cc: pgsql-sql@postgresql.org Subject: Re: SQL query help! References: Content-Type: multipart/signed; protocol="application/x-pkcs7-signature"; micalg=sha1; boundary="------------ms080209060900030807050408" X-Virus-Scanned: by AMaViS new-20020517 X-Razor-id: 7fec2853d0fd8381bb997dbc46e7238b202f04ce X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200211/414 X-Sequence-Number: 10556 This is a cryptographically signed message in MIME format. --------------ms080209060900030807050408 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit Tell me what did you try with limit and group by. Where's IN, why don't you use EXISTS instead. It runs much master ! Regards, Luis Sousa Arcadius A. wrote: >Hello! > >I hope that someone here could help. > >I'm using PostgreSQL7.1.3 > >I have 3 tables in my DB: the tables are defined in the following way: > > >CREATE TABLE category( >id SERIAL NOT NULL PRIMARY KEY, >// etc etc > >) >; > >CREATE TABLE subcategory( >id SERIAL NOT NULL PRIMARY KEY, >categoryid int CONSTRAINT subcategory__ref_category > REFERENCES category (id) > // etc etc >) >; > >CREATE TABLE entry( >entryid SERIAL NOT NULL PRIMARY KEY, >isapproved CHAR(1) NOT NULL DEFAULT 'n', >subcategoryid int CONSTRAINT entry__ref_subcategory > REFERENCES subcategory (id) > // atd >, >) >; > > >I have the following SQL query : > > "SELECT * FROM entry where isapproved='y' AND subcategoryid IN (SELECT id >FROM subcategory WHERE >categoryid='"+catID+"') ORDER BY subcategoryid DESC"; > > >For a given categoryid( catID), the query will return all entries in the >"entry" table >having a corresponding subcategoryid(s)[returned by the inned subquery]. > >But I want to return only a limited number of entries of each >subcategory..... let's say that I want to return at most 5 entries of each >subcategory type ( for instance if the inner subquery returns 3 results, >thus I will be having in total at most 15 entries as relust).... > >How can this be achieved? > >I'm aware of postgreSQL "LIMIT" and "GROUP BY" clause..... but so far, I'm >not able to put all this together... > >Thanks in advance. > >Arcadius. > > > > > > >---------------------------(end of broadcast)--------------------------- >TIP 4: Don't 'kill -9' the postmaster > > > > --------------ms080209060900030807050408 Content-Type: application/x-pkcs7-signature; name="smime.p7s" Content-Transfer-Encoding: base64 Content-Disposition: attachment; filename="smime.p7s" Content-Description: S/MIME Cryptographic Signature MIAGCSqGSIb3DQEHAqCAMIACAQExCzAJBgUrDgMCGgUAMIAGCSqGSIb3DQEH AQAAoIIJPDCCAvwwggJloAMCAQICAwcgpDANBgkqhkiG9w0BAQQFADCBkjEL MAkGA1UEBhMCWkExFTATBgNVBAgTDFdlc3Rlcm4gQ2FwZTESMBAGA1UEBxMJ Q2FwZSBUb3duMQ8wDQYDVQQKEwZUaGF3dGUxHTAbBgNVBAsTFENlcnRpZmlj YXRlIFNlcnZpY2VzMSgwJgYDVQQDEx9QZXJzb25hbCBGcmVlbWFpbCBSU0Eg MjAwMC44LjMwMB4XDTAyMDQwMjEzNDYxNloXDTAzMDQwMjEzNDYxNlowQTEf MB0GA1UEAxMWVGhhd3RlIEZyZWVtYWlsIE1lbWJlcjEeMBwGCSqGSIb3DQEJ ARYPbGxzb3VzYUB1YWxnLnB0MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIB CgKCAQEArq+CLmsIQV5pSRtuNd9sptT22gTUwt0UReWfSlCTApnNcGDQCiS8 nluhr5AWbJWhhIVHQETSKWlT+y09KsYf7AeW1zBQWSUJjhpCDYd8n+T0SxFl 24ZZJhZQ57kVJ7iNzJ7WPYQGk06zoRWgtnFcvVuA1az0vhwWHNk8+1JaCTeW lwjPRptJZ78CNiN2prAoEoI9m7/jUW6iTJmpNinzkIa1qpKEdPrCQM/dhuk6 JDl4qmiQ2a87I/aiRJCPtTBIl/Zt2Vjde5Br2qR8pWisjaoVl9ntJJvni9cl XJGk/uO4iwza7IL2K6PsRvbGAoswWKeEStK+A/WXoh1UXbHEyQIDAQABoyww KjAaBgNVHREEEzARgQ9sbHNvdXNhQHVhbGcucHQwDAYDVR0TAQH/BAIwADAN BgkqhkiG9w0BAQQFAAOBgQCee996SyMsQgWwQUrnHsmltoIMnkkLuWvWfWwq 3fqe8ZLpfvXvFg6Z52EtPZ8vlzp41KM1HQErW2dMe2DyUfbhaUqqNQBvf67V G8STIA+83VEKFK2njzIKpwLXOOjL0estNpcrE1azR/MTfvvu3adO6j5syXd6 pfm4elI2W0yLMzCCAvwwggJloAMCAQICAwcgpDANBgkqhkiG9w0BAQQFADCB kjELMAkGA1UEBhMCWkExFTATBgNVBAgTDFdlc3Rlcm4gQ2FwZTESMBAGA1UE BxMJQ2FwZSBUb3duMQ8wDQYDVQQKEwZUaGF3dGUxHTAbBgNVBAsTFENlcnRp ZmljYXRlIFNlcnZpY2VzMSgwJgYDVQQDEx9QZXJzb25hbCBGcmVlbWFpbCBS U0EgMjAwMC44LjMwMB4XDTAyMDQwMjEzNDYxNloXDTAzMDQwMjEzNDYxNlow QTEfMB0GA1UEAxMWVGhhd3RlIEZyZWVtYWlsIE1lbWJlcjEeMBwGCSqGSIb3 DQEJARYPbGxzb3VzYUB1YWxnLnB0MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8A MIIBCgKCAQEArq+CLmsIQV5pSRtuNd9sptT22gTUwt0UReWfSlCTApnNcGDQ CiS8nluhr5AWbJWhhIVHQETSKWlT+y09KsYf7AeW1zBQWSUJjhpCDYd8n+T0 SxFl24ZZJhZQ57kVJ7iNzJ7WPYQGk06zoRWgtnFcvVuA1az0vhwWHNk8+1Ja CTeWlwjPRptJZ78CNiN2prAoEoI9m7/jUW6iTJmpNinzkIa1qpKEdPrCQM/d huk6JDl4qmiQ2a87I/aiRJCPtTBIl/Zt2Vjde5Br2qR8pWisjaoVl9ntJJvn i9clXJGk/uO4iwza7IL2K6PsRvbGAoswWKeEStK+A/WXoh1UXbHEyQIDAQAB oywwKjAaBgNVHREEEzARgQ9sbHNvdXNhQHVhbGcucHQwDAYDVR0TAQH/BAIw ADANBgkqhkiG9w0BAQQFAAOBgQCee996SyMsQgWwQUrnHsmltoIMnkkLuWvW fWwq3fqe8ZLpfvXvFg6Z52EtPZ8vlzp41KM1HQErW2dMe2DyUfbhaUqqNQBv f67VG8STIA+83VEKFK2njzIKpwLXOOjL0estNpcrE1azR/MTfvvu3adO6j5s yXd6pfm4elI2W0yLMzCCAzgwggKhoAMCAQICEGZFcrfMdPXPY3ZFhNAukQEw DQYJKoZIhvcNAQEEBQAwgdExCzAJBgNVBAYTAlpBMRUwEwYDVQQIEwxXZXN0 ZXJuIENhcGUxEjAQBgNVBAcTCUNhcGUgVG93bjEaMBgGA1UEChMRVGhhd3Rl IENvbnN1bHRpbmcxKDAmBgNVBAsTH0NlcnRpZmljYXRpb24gU2VydmljZXMg RGl2aXNpb24xJDAiBgNVBAMTG1RoYXd0ZSBQZXJzb25hbCBGcmVlbWFpbCBD QTErMCkGCSqGSIb3DQEJARYccGVyc29uYWwtZnJlZW1haWxAdGhhd3RlLmNv bTAeFw0wMDA4MzAwMDAwMDBaFw0wNDA4MjcyMzU5NTlaMIGSMQswCQYDVQQG EwJaQTEVMBMGA1UECBMMV2VzdGVybiBDYXBlMRIwEAYDVQQHEwlDYXBlIFRv d24xDzANBgNVBAoTBlRoYXd0ZTEdMBsGA1UECxMUQ2VydGlmaWNhdGUgU2Vy dmljZXMxKDAmBgNVBAMTH1BlcnNvbmFsIEZyZWVtYWlsIFJTQSAyMDAwLjgu MzAwgZ8wDQYJKoZIhvcNAQEBBQADgY0AMIGJAoGBAN4zMqZjxwklRT7Sbngn Z4HF2ogZgpcO40QpimM1Km1wPPrcrvfudG8wvDOQf/k0caCjbZjxw0+iZdsN +kvx1t1hpfmFzVWaNRqdknWoJ67Ycvm6AvbXsJHeHOmr4BgDqHxDQlBRh4M8 8Dm0m1SKE4f/s5udSWYALQmJ7JRr6aFpAgMBAAGjTjBMMCkGA1UdEQQiMCCk HjAcMRowGAYDVQQDExFQcml2YXRlTGFiZWwxLTI5NzASBgNVHRMBAf8ECDAG AQH/AgEAMAsGA1UdDwQEAwIBBjANBgkqhkiG9w0BAQQFAAOBgQAxsUtHXfkB ceX1U2xdedY9mMAmE2KBIqcS+CKV6BtJtyd7BDm6/ObyJOuR+r3sDSo491BV qGz3Da1MG7wD9LXrokefbKIMWI0xQgkRbLAaadErErJAXWr5edDqLiXdiuT8 2w0fnQLzWtvKPPZE6iZph39Ins6ln+eE2MliYq0FxjGCAycwggMjAgEBMIGa MIGSMQswCQYDVQQGEwJaQTEVMBMGA1UECBMMV2VzdGVybiBDYXBlMRIwEAYD VQQHEwlDYXBlIFRvd24xDzANBgNVBAoTBlRoYXd0ZTEdMBsGA1UECxMUQ2Vy dGlmaWNhdGUgU2VydmljZXMxKDAmBgNVBAMTH1BlcnNvbmFsIEZyZWVtYWls IFJTQSAyMDAwLjguMzACAwcgpDAJBgUrDgMCGgUAoIIBYTAYBgkqhkiG9w0B CQMxCwYJKoZIhvcNAQcBMBwGCSqGSIb3DQEJBTEPFw0wMjExMjcxMDA1MjRa MCMGCSqGSIb3DQEJBDEWBBR8+8wuuAJ4aptg0LWGbHeQvOCJYjBSBgkqhkiG 9w0BCQ8xRTBDMAoGCCqGSIb3DQMHMA4GCCqGSIb3DQMCAgIAgDANBggqhkiG 9w0DAgIBQDAHBgUrDgMCBzANBggqhkiG9w0DAgIBKDCBrQYLKoZIhvcNAQkQ AgsxgZ2ggZowgZIxCzAJBgNVBAYTAlpBMRUwEwYDVQQIEwxXZXN0ZXJuIENh cGUxEjAQBgNVBAcTCUNhcGUgVG93bjEPMA0GA1UEChMGVGhhd3RlMR0wGwYD VQQLExRDZXJ0aWZpY2F0ZSBTZXJ2aWNlczEoMCYGA1UEAxMfUGVyc29uYWwg RnJlZW1haWwgUlNBIDIwMDAuOC4zMAIDByCkMA0GCSqGSIb3DQEBAQUABIIB ACoyeeibxPHg+JMF4HdULl5nBjfF+oPLWhhPwlSgnfZyPaKH94KPxVfs3SJz LvwmYvxXZ+il3U/WCogBJKi9tN1E+wTvDQns5YvIDtfnnptHh01W0ApNpxzD btE+bgSTK5dqRsOtHhO4QRzIwUuchXUhvZVTP0na5BIi82BXRntHWkicaE/R ZtSZo4DuUZyF982FgkHwsakVwXd5HoIPiF6apBZKR26XkJuIElyvfxmoqSJB hPIQO8Habbs0ssBJwBeLDfDA4KASsD/juk6JEtLTZEawI/He0LV4/9l3LQHa DXhctQnxE3YZC4FOqHDRy4WeIMqHS/AmBA8ChtOb3MsAAAAAAAA= --------------ms080209060900030807050408--