Latest ads

Electronics
Vehicles
Property
Search

wanna help abt SQL...(urgent)

ad.hasareli

ad.hasareli

Well-known member
  • Mar 27, 2010
    5,264
    580
    113
    දකුනේ කොල්ලෙක්
    NO_MeRcY said:
    Code: 
    select itemcode,max(loadingNumber)
from TEST
group by itemcode


select itemcode,max(loadingNumber),itemname
from TEST
group by itemcode,itemname
    uba kiyanne 2nd query eken ena data repeat wenawa kiyalada? eka wenna baha. 2nd query eke mul ekata wada wenas wenne itemname column eka add wena ekay itemname eken group by wena ekay.

    poddak dekama run karala balala kiyapan machang deken return wena rows gana wenas da kiyala . dekenma 4 rows neda return wenne.
    Click to expand...



    ath same machan......:( menna screen short 2ma

    1.jpg


    2.jpg
     
    tsomeone

    tsomeone

    Well-known member
  • Jul 29, 2012
    4,028
    620
    113
    ad.hasareli said:
    ath same machan......:( menna screen short 2ma

    1.jpg


    2.jpg
    Click to expand...
    select itemcode,max(loadingNumber) from TEST group by itemcode MEEKA MEHEMA DANNA

    select itemcode,itemname from TEST group by itemcode,itemnameOYOya widyata quary deka hadaganin machan, machan quary deka hari awula thiyenne data wala eekai data repeat ekak wenne man print screen eken dakke
    balahan same item name thiyna itemcode dekak tiyenawane ey ban ehema une eekai repeat ekak wenne so basically oyat tiynne data quary deka wenama save karala relationship ekak dala left outer join hari right outer join ekakin 3rd quary eka goda danna etakot repeat weemak nomatha eet ube data wala case tiyenawa balapan hariyat data dala

    Itemname eka thamai case eka ekama itemcode ekat ey wena wena itemname enne?
    ehema nowiya yuthui data hariyat enter karapan kollo nettan kochchara wada karat wadak ne

    ex: applepuff100 has 1001 & 1002
     
    Last edited:
    • Like
    Reactions: ad.hasareli
    san1020

    san1020

    Well-known member
  • Jun 8, 2011
    3,496
    506
    113
    ලැප ඉදිරියේ
    tsomeone said:
    select itemcode,max(loadingNumber) from TEST group by itemcode MEEKA MEHEMA DANNA

    select itemcode,itemname from TEST group by itemcode,itemnameOYOya widyata quary deka hadaganin machan, machan quary deka hari awula thiyenne data wala eekai data repeat ekak wenne man print screen eken dakke
    balahan same item name thiyna itemcode dekak tiyenawane ey ban ehema une eekai repeat ekak wenne so basically oyat tiynne data quary deka wenama save karala relationship ekak dala left outer join hari right outer join ekakin 3rd quary eka goda danna etakot repeat weemak nomatha eet ube data wala case tiyenawa balapan hariyat data dala
    Click to expand...


    :yes::yes::yes::yes::yes::yes::yes:
     
    • Like
    Reactions: ad.hasareli
    tsomeone

    tsomeone

    Well-known member
  • Jul 29, 2012
    4,028
    620
    113
    itemname,max(loadingNumber) as MaxLoad
    from TEST
    group by itemcode



    ANIKA OYA BOLD KARALA THIYENA KOTASA DANNA NETTAN KOHOMADA FIELD EKA GANNE DATASET EKAT?
     
    tsomeone

    tsomeone

    Well-known member
  • Jul 29, 2012
    4,028
    620
    113
    ad.hasareli said:
    datat akka ganne kohomada machan ?????????? :oo:
    Click to expand...
    MACHAN KOHOMAHARI KATTA KALA HOYAGANIN SOLUTIONS OYA ENA EWA CLASS WALINWAT SIRLAGENWAT UTTARA ENNE NA THAMANGE UTHASAHAYEN GODA DAGANNA PULUWAN OOKA AGATA AWAMA PRACTICE UNAMA SOLUTION ENAWA OOKA HARI GIYE NATHI UNAMA WATENAWA MAANASIKAWA

    EEWAGEDA MACHAN QUARRY EKA GODA GIYAMA MARA FEELING EKA UBA HITHALA LIYANA QUARRY EKA NEWI MAN LIYANNE EWA UNIQUE SATUTU WEYAN UBA OOKAT OCHCHRA MARENA EKAT, KAWRUTH ATHARALA DALA NIKAN INNAWA. PRACTICALLY ENA ERRORS WALIN SAHENNA DEWAL PRACTICE WENAWA, TRY MACHAN TRY GOOD LUCK
     
    ad.hasareli

    ad.hasareli

    Well-known member
  • Mar 27, 2010
    5,264
    580
    113
    දකුනේ කොල්ලෙක්
    tsomeone said:
    MACHAN KOHOMAHARI KATTA KALA HOYAGANIN SOLUTIONS OYA ENA EWA CLASS WALINWAT SIRLAGENWAT UTTARA ENNE NA THAMANGE UTHASAHAYEN GODA DAGANNA PULUWAN OOKA AGATA AWAMA PRACTICE UNAMA SOLUTION ENAWA OOKA HARI GIYE NATHI UNAMA WATENAWA MAANASIKAWA

    EEWAGEDA MACHAN QUARRY EKA GODA GIYAMA MARA FEELING EKA UBA HITHALA LIYANA QUARRY EKA NEWI MAN LIYANNE EWA UNIQUE SATUTU WEYAN UBA OOKAT OCHCHRA MARENA EKAT, KAWRUTH ATHARALA DALA NIKAN INNAWA. PRACTICALLY ENA ERRORS WALIN SAHENNA DEWAL PRACTICE WENAWA, TRY MACHAN TRY GOOD LUCK
    Click to expand...

    tsomeone said:
    itemname,max(loadingNumber) as MaxLoad
    from TEST
    group by itemcode



    ANIKA OYA BOLD KARALA THIYENA KOTASA DANNA NETTAN KOHOMADA FIELD EKA GANNE DATASET EKAT?
    Click to expand...


    shanehbrsj said:
    bump
    Click to expand...

    oyala hemotama thanx qwa ehenam.........
     
    ad.hasareli

    ad.hasareli

    Well-known member
  • Mar 27, 2010
    5,264
    580
    113
    දකුනේ කොල්ලෙක්
    tsomeone said:
    select itemcode,max(loadingNumber) from TEST group by itemcode MEEKA MEHEMA DANNA

    select itemcode,itemname from TEST group by itemcode,itemnameOYOya widyata quary deka hadaganin machan, machan quary deka hari awula thiyenne data wala eekai data repeat ekak wenne man print screen eken dakke
    balahan same item name thiyna itemcode dekak tiyenawane ey ban ehema une eekai repeat ekak wenne so basically oyat tiynne data quary deka wenama save karala relationship ekak dala left outer join hari right outer join ekakin 3rd quary eka goda danna etakot repeat weemak nomatha eet ube data wala case tiyenawa balapan hariyat data dala

    Itemname eka thamai case eka ekama itemcode ekat ey wena wena itemname enne?
    ehema nowiya yuthui data hariyat enter karapan kollo nettan kochchara wada karat wadak ne

    ex: applepuff100 has 1001 & 1002
    Click to expand...


    machan itemcode aka duplicate une mama poddak ithin wenas kara kara balanawa wede kohomada wenne kiyala mko ban me hema deyakma karanne igana gannane ban... akai une... podi mistake akak nisa tamai itemcode aka dublicate une....
     
    san1020

    san1020

    Well-known member
  • Jun 8, 2011
    3,496
    506
    113
    ලැප ඉදිරියේ
    2vbq5n5.jpg


    Code: 
    SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';

CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
USE `mydb` ;

-- -----------------------------------------------------
-- Table `mydb`.`item`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`item` (
  `itemid` INT NOT NULL ,
  `itemname` VARCHAR(45) NULL ,
  PRIMARY KEY (`itemid`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`lorry`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`lorry` (
  `lorryid` INT NOT NULL ,
  `lorry name` VARCHAR(45) NULL ,
  PRIMARY KEY (`lorryid`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`loading`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`loading` (
  `itemid` INT NOT NULL ,
  `lorryid` INT NOT NULL ,
  `date` DATE NULL ,
  `loadedno` INT NULL ,
  PRIMARY KEY (`itemid`, `lorryid`) ,
  INDEX `fk_loading_item` (`itemid` ASC) ,
  INDEX `fk_loading_lorry1` (`lorryid` ASC) ,
  CONSTRAINT `fk_loading_item`
    FOREIGN KEY (`itemid` )
    REFERENCES `mydb`.`item` (`itemid` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_loading_lorry1`
    FOREIGN KEY (`lorryid` )
    REFERENCES `mydb`.`lorry` (`lorryid` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;



SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
     
    san1020

    san1020

    Well-known member
  • Jun 8, 2011
    3,496
    506
    113
    ලැප ඉදිරියේ
    SQL dump
    Code: 
    CREATE DATABASE  IF NOT EXISTS `mydb` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `mydb`;
-- MySQL dump 10.13  Distrib 5.5.16, for Win32 (x86)
--
-- Host: localhost    Database: mydb
-- ------------------------------------------------------
-- Server version    5.5.25a

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `item`
--

DROP TABLE IF EXISTS `item`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `item` (
  `itemid` int(11) NOT NULL,
  `itemname` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`itemid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `item`
--

LOCK TABLES `item` WRITE;
/*!40000 ALTER TABLE `item` DISABLE KEYS */;
INSERT INTO `item` VALUES (1001,'marie'),(1002,'marie 100'),(1003,'apple puff'),(1004,'apple puff 100');
/*!40000 ALTER TABLE `item` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `loading`
--

DROP TABLE IF EXISTS `loading`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `loading` (
  `loadingid` int(11) NOT NULL,
  `itemid` int(11) NOT NULL,
  `lorryid` int(11) NOT NULL,
  `date` date DEFAULT NULL,
  `loadedno` int(11) DEFAULT NULL,
  PRIMARY KEY (`loadingid`),
  KEY `fk_loading_item` (`itemid`),
  KEY `fk_loading_lorry1` (`lorryid`),
  CONSTRAINT `fk_loading_item` FOREIGN KEY (`itemid`) REFERENCES `item` (`itemid`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_loading_lorry1` FOREIGN KEY (`lorryid`) REFERENCES `lorry` (`lorryid`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `loading`
--

LOCK TABLES `loading` WRITE;
/*!40000 ALTER TABLE `loading` DISABLE KEYS */;
INSERT INTO `loading` VALUES (1,1001,1,'2012-07-01',100),(2,1001,2,'2012-07-02',150),(3,1002,1,'2012-07-02',200),(4,1003,2,'2012-07-01',200);
/*!40000 ALTER TABLE `loading` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `lorry`
--

DROP TABLE IF EXISTS `lorry`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `lorry` (
  `lorryid` int(11) NOT NULL,
  `lorry name` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`lorryid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `lorry`
--

LOCK TABLES `lorry` WRITE;
/*!40000 ALTER TABLE `lorry` DISABLE KEYS */;
INSERT INTO `lorry` VALUES (1,'lorry1'),(2,'lorry2');
/*!40000 ALTER TABLE `lorry` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2012-08-02 21:40:36

    Select query
    Code: 
    SELECT distinct loading.itemid,item.itemname,loading.date from loading left join item on item.itemid=loading.itemid group by itemid order by date desc;

    හරි යයි කියල හිතනවා , වැරදි නම් වෙනස් කරලා බලන්න query එක .
     
    NO_MeRcY

    NO_MeRcY

    Well-known member
  • Jun 14, 2010
    5,423
    449
    83
    Singapore
    this is the query he wanted :D

    Code: 
    SELECT loadingNumber,itemname,itemcode,lorrystock,Loading,lorry 
FROM loading 
WHERE loadingNumber=(
    SELECT max(loadingNumber) FROM loading
    )
     
    Last edited:
    ad.hasareli

    ad.hasareli

    Well-known member
  • Mar 27, 2010
    5,264
    580
    113
    දකුනේ කොල්ලෙක්
    san1020 said:
    SQL dump
    Code: 
    CREATE DATABASE  IF NOT EXISTS `mydb` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `mydb`;
-- MySQL dump 10.13  Distrib 5.5.16, for Win32 (x86)
--
-- Host: localhost    Database: mydb
-- ------------------------------------------------------
-- Server version    5.5.25a

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `item`
--

DROP TABLE IF EXISTS `item`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `item` (
  `itemid` int(11) NOT NULL,
  `itemname` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`itemid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `item`
--

LOCK TABLES `item` WRITE;
/*!40000 ALTER TABLE `item` DISABLE KEYS */;
INSERT INTO `item` VALUES (1001,'marie'),(1002,'marie 100'),(1003,'apple puff'),(1004,'apple puff 100');
/*!40000 ALTER TABLE `item` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `loading`
--

DROP TABLE IF EXISTS `loading`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `loading` (
  `loadingid` int(11) NOT NULL,
  `itemid` int(11) NOT NULL,
  `lorryid` int(11) NOT NULL,
  `date` date DEFAULT NULL,
  `loadedno` int(11) DEFAULT NULL,
  PRIMARY KEY (`loadingid`),
  KEY `fk_loading_item` (`itemid`),
  KEY `fk_loading_lorry1` (`lorryid`),
  CONSTRAINT `fk_loading_item` FOREIGN KEY (`itemid`) REFERENCES `item` (`itemid`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_loading_lorry1` FOREIGN KEY (`lorryid`) REFERENCES `lorry` (`lorryid`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `loading`
--

LOCK TABLES `loading` WRITE;
/*!40000 ALTER TABLE `loading` DISABLE KEYS */;
INSERT INTO `loading` VALUES (1,1001,1,'2012-07-01',100),(2,1001,2,'2012-07-02',150),(3,1002,1,'2012-07-02',200),(4,1003,2,'2012-07-01',200);
/*!40000 ALTER TABLE `loading` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `lorry`
--

DROP TABLE IF EXISTS `lorry`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `lorry` (
  `lorryid` int(11) NOT NULL,
  `lorry name` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`lorryid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `lorry`
--

LOCK TABLES `lorry` WRITE;
/*!40000 ALTER TABLE `lorry` DISABLE KEYS */;
INSERT INTO `lorry` VALUES (1,'lorry1'),(2,'lorry2');
/*!40000 ALTER TABLE `lorry` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2012-08-02 21:40:36
    Select query
    Code: 
    SELECT distinct loading.itemid,item.itemname,loading.date from loading left join item on item.itemid=loading.itemid group by itemid order by date desc;
    හරි යයි කියල හිතනවා , වැරදි නම් වෙනස් කරලා බලන්න query එක .
    Click to expand...


    machan thanx thanx thanx a lot machan...............................

    NO_MeRcY said:
    this is the query he wanted :D

    Code: 
    SELECT loadingNumber,itemname,itemcode,lorrystock,Loading,lorry 
FROM loading 
WHERE loadingNumber=(
    SELECT max(loadingNumber) FROM loading
    )
    Click to expand...

    :yes::yes::yes::yes::yes::P:love:
     
    tsomeone

    tsomeone

    Well-known member
  • Jul 29, 2012
    4,028
    620
    113
    NO_MeRcY said:
    this is the query he wanted :D

    Code: 
    SELECT loadingNumber,itemname,itemcode,lorrystock,Loading,lorry 
FROM loading 
WHERE loadingNumber=(
    SELECT max(loadingNumber) FROM loading
    )
    Click to expand...


    ela subquary saves a day machan :nerd: godak help wenawa samahara dawaswalata:yes:
     

    Similar threads

    ad.hasareli
    Programing udauwak plzzzzzz
    Replies
    32
    Views
    3K
    chirart1
    C
    ad.hasareli
    SQL help :(
    Replies
    5
    Views
    690
    ad.hasareli
    ad.hasareli
    ad.hasareli
    SQL SEVER
    Replies
    119
    Views
    7K
    hirashgeff
    hirashgeff
    ad.hasareli
    hellp help..... .net(vb) urgent :(
    Replies
    17
    Views
    1K
    penguinruwan
    penguinruwan
    ad.hasareli
    vb.net
    Replies
    19
    Views
    2K
    Kasun k
    Kasun k
    Top Bottom